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

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

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

  1. 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,

  2. 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

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

  4. 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

  5. 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

  6. 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

  7. 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

  8. 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.

  9. 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.

Leave a Reply

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