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

8 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
  4. Sunil Shah

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

    Reply
  5. Sunil

    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

    Reply
  6. yusuf abdallah

    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

    Reply

Leave a Reply

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