How to get data from worksheet into combobox and textboxes on user form

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
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
End Sub
Private Sub UserForm_Click()
End Sub

3 thoughts on “How to get data from worksheet into combobox and textboxes on user form

  1. Michael

    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,

    Reply
  2. Bruno

    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

    Reply
  3. Lance Meroniuk

    Code does not work… stops at
    LastRow = Sheets(“Sheet1”).Range(“A” & Rows.Count).End(xlUp).Row

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *