How to display data in Listbox based on Combo Box selection automatically. A user has setup a user-form with a combo-box and a list-box. The combo-box should capture the IDs from the data in a worksheet. On selection from the combo-box, i. e. on selection change the listbox should automatically be populated from the other columns from the worksheet. The list-box will now contain the ID, with corresponding customer name, address, phone and email from the worksheet. watch the video below:

Watch this video on YouTube.

Here’s the complete VBA Code:

Option Explicit
Dim myData As Range
Private Sub ComboBox1_Change()
Me.ListBox1.ListIndex = Me.ComboBox1.ListIndex
Dim mySearchRng As Range
Dim myFindRng As Range
Dim myValToFind As Variant

With Worksheets(“Sheet1”)
myValToFind = ComboBox1.Value ‘Name is ComboBox1
Set mySearchRng = .Columns(“A”)
End With

Set myFindRng = mySearchRng.Find(What:=myValToFind, _
        LookIn:=xlFormulas, _
        LookAt:=xlWhole, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=False)

    ListBox1.AddItem
    With ListBox1
        .List(.ListCount - 1, 0) = myFindRng.Value  'Data from Col A
        .List(.ListCount - 1, 1) = myFindRng.Offset(0, 2).Value 'Data from Col B
        .List(.ListCount - 1, 2) = myFindRng.Offset(0, 3).Value 'Data from Col C
        .List(.ListCount - 1, 3) = myFindRng.Offset(0, 4).Value 'Data from Col D
        .List(.ListCount - 1, 4) = myFindRng.Offset(0, 5).Value 'Data from Col E
    End With

End Sub

Private Sub CommandButton1_Click()
Unload Me
End Sub

Private Sub UserForm_Initialize()
Set myData = Sheet1.Range(“A1”).CurrentRegion
Me.ComboBox1.List = myData.Offset(1).Value
Me.ListBox1.ColumnCount = 5
Me.ListBox1.List = Me.ComboBox1.List

End Sub

Further Reading:

Linking combo box to list box on userform

Download a sample file for practice: