Listbox to Textbox within Worksheet

Boechat

New Member
Joined
Jul 16, 2016
Messages
44
Greetings everyone,

I've recently started programming with VBA outside of UserForms and directly on the worksheets. I'm struggling however with populating a TextBox based on ListBox.Click.

Let's say for instance my ListBox is filled with all the information from Worksheet 1, column A and I want the TextBox to show me whatever information I have on column B of the same row as the selected ListBox item.

On UserForm I'm used to achieve this by using ListBox_Click and Textbox1.value equals ListBox1.item + offset

How could this be done in the worksheet?

Thanks everyone!
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,256
Office Version
  1. 2013
Platform
  1. Windows
Try this:
Code:
Private Sub ListBox1_Click()
'Modified  4/11/2019  3:50:26 AM  EDT
Dim SearchString As String
Dim SearchRange As Range
SearchString = ListBox1.Value
Dim lastrow As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set SearchRange = Range("A1:A" & lastrow).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
If SearchRange Is Nothing Then MsgBox SearchString & "  Not Found": Exit Sub
TextBox1.Value = SearchRange.Offset(0, 1).Value
End Sub
 

Boechat

New Member
Joined
Jul 16, 2016
Messages
44
Hey, thanks for your support!

However the code bugs at SearchString = ListBox1.Value and mentions that it doesn't find the object.
Perhaps my ListBox isn't named correctly, however I can't find where to review its properties...

Any thoughts?
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,256
Office Version
  1. 2013
Platform
  1. Windows
So how did you get all the values from column A into the Listbox?
You said in Post 1
Let's say for instance my ListBox is filled with all the information from Worksheet 1, column A

You needed a script to do that.
So what was the name of the listbox you had in your script to load the values in the listbox?

 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,203
Office Version
  1. 365
Platform
  1. Windows
Cross posted https://www.excelforum.com/excel-programming-vba-macros/1272137-listbox-to-textbox-on-worksheet.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,110
Messages
5,599,773
Members
414,336
Latest member
Nicolas2465

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top