How can we retrieve data from an Excel worksheet into a combo-box and text-boxes into a user-form with VBA. A user wants to get all the IDs in column A into a combo-box automatically. Next when he selects an ID he wishes to get data from specific columns into the text-boxes on his user-form automatically. This is like doing a Vlookup in Excel automatically. Watch the training video below:
You can view this video also on YouTube.
Here’s the complete code to populate a combo-box with data from a worksheet and then do a Vlookup automatically from specific worksheet columns:
Private Sub ComboBox1_DropButtonClick()
Dim i As Long, LastRow As Long
LastRow = Sheets(“Sheet1”).Range(“A” & Rows.Count).End(xlUp).Row
If Me.ComboBox1.ListCount = 0 Then
For i = 2 To LastRow
Me.ComboBox1.AddItem Sheets(“Sheet1”).Cells(i, “A”).Value
Next i
End If
Dim i As Long, LastRow As Long
LastRow = Sheets(“Sheet1”).Range(“A” & Rows.Count).End(xlUp).Row
If Me.ComboBox1.ListCount = 0 Then
For i = 2 To LastRow
Me.ComboBox1.AddItem Sheets(“Sheet1”).Cells(i, “A”).Value
Next i
End If
End Sub
Private Sub ComboBox1_Change()
Dim i As Long, LastRow As Long
LastRow = Sheets(“Sheet1”).Range(“A” & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
If Sheets(“Sheet1”).Cells(i, “A”).Value = (Me.ComboBox1) Or _
Sheets(“Sheet1”).Cells(i, “A”).Value = Val(Me.ComboBox1) Then
Me.TextBox1 = Sheets(“Sheet1”).Cells(i, “B”).Value
Me.TextBox2 = Sheets(“Sheet1”).Cells(i, “E”).Value
End If
Next
Dim i As Long, LastRow As Long
LastRow = Sheets(“Sheet1”).Range(“A” & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
If Sheets(“Sheet1”).Cells(i, “A”).Value = (Me.ComboBox1) Or _
Sheets(“Sheet1”).Cells(i, “A”).Value = Val(Me.ComboBox1) Then
Me.TextBox1 = Sheets(“Sheet1”).Cells(i, “B”).Value
Me.TextBox2 = Sheets(“Sheet1”).Cells(i, “E”).Value
End If
Next
End Sub
Private Sub UserForm_Click()
End Sub
hi,
my problem is I have a combo box in sheet1 and the value of that combo box should be in sheet 2 how can I populate it?
Thanks,
Hello,
Thank for for all your help and making the codes available.
I have written an almost similar code ( see below )
The only difference is that my first column is a date one.
When I try to run the code there is an error on the following line preventing the code to continue to run
LastRow = Sheets(“Sheet1”).Range(“A” & Rows.Count).End(xlUp).Row
I am not sure why as my knowledge is limited in vab and wondered whether you could help and let me know why.
Thanks in advance
Bruno
Private Sub ComboBox1_DropButtonClick()
Dim i As Long, LastRow As Long
LastRow = Sheets(“Sheet1”).Range(“A” & Rows.Count).End(xlUp).Row
If Me.ComboBox1.ListCount = 0 Then
For i = 2 To LastRow
Me.ComboBox1.AddItem Sheets(“Sheet1”).Cells(i, “A”).Value
Next i
End If
End Sub
Private Sub ComboBox1_Change()
Dim i As Long, LastRow As Long
LastRow = Sheets(“Sheet1”).Range(“A” & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
If Sheets(“Sheet1”).Cells(i, “A”).Value = (Me.ComboBox1) Or _
Sheets(“Sheet1”).Cells(i, “A”).Value = Val(Me.ComboBox1) Then
Me.TextBox1 = Sheets(“Sheet1”).Cells(i, “D”).Value
Me.TextBox2 = Sheets(“Sheet1”).Cells(i, “E”).Value
End If
Next
End Sub
change the quotes
Code does not work… stops at
LastRow = Sheets(“Sheet1”).Range(“A” & Rows.Count).End(xlUp).Row
Code does not work… stops at
LastRow = Sheets(“Sheet1”).Range(“A” & Rows.Count).End(xlUp).Row
Code does not work… stops at
LastRow = Sheets(“Sheet1”).Range(“A” & Rows.Count).End(xlUp).Row
Kindly upload excel file if possible. with code and sample data
while pasting the code, pl replace “ (wherever found) with “
Code doesnt work. please assist. same problem like the others.
serial no. name Telphone previous kwts current kws usage Rate amount
julC1 CAABI 615666662 399 419 20 1.3 $26.00
i use such type of excel i want help to get a userform which can help me to fiil large amount of date in limited time
for example a usserfor which has blank current kwts and if i fill that blank space it calculayes the rest and saves according to the serial number
It display nothing, Can anyone help please.
There is my code
Private Sub ComboBox1_DropButtonClick()
Dim i As Long, LastRow As Long
LastRow = Sheets(“Sheet1”).Range(“A” & Rows.Count).End(xlUp).Row
If Me.ComboBox1.ListCount = 0 Then
For i = 2 To LasRow
Me.ComboBox1.AddItem Sheets(“Sheet1”).Cells(i, “A”).Value
Next i
End If
End Sub
Private Sub ComboBox1_Change()
Dim i As Long, LastRow As Long
LastRow = Sheets(“Sheet1”).Range(“A” & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
If Sheets(“Sheet1”).Cells(i, “A”).Value = (Me.ComboBox1) Or _
Sheets(“Sheet1”).Cell(i, “A”).Value = Val(Me.ComboBox1) Then
Me.TextBox1 = Sheets(“Sheet1”).Cells(i, “B”).Value
End If
Next
End Sub
Private Sub UserForm_Click()
End Sub
and my range A1 is component and B1 is price.
Thanks
change the quotes
Thank you so much for help for code to “get data from worksheet into combobox and textboxes on user form”
Dear Sir
I have watched your video a few times now and have copied and pasted the code you have above to the combobox but it stops at…. LastRow = Sheets(“List”).Range(“A” & Rows.Count).End(xlUp).Row
My combobox is populated by rowsource in the properties by a dynamic list called “Name” could that be the trouble
change the quotes
when we run this program find subscript out of range run time 9 error
I’ll tell you guys who found the codes not to be working, act your brain. Don’t just always copy-paste the code- type the code. Some may found the code to be wrong in quotes. Change them, your code will work definitely. Thank you for understanding.
Hello, first I would like to thank you for Sharing your knowledge, I have been Struggling with the Quotes like a few others have. It was strange because I change the quotes and the issues still occurred. But after a while, it seemed to work.
Just wanted to say thank you.