Display Data in Listbox based on Combo Box selection

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:

Display data in Listbox based on Combo Box selection automatically

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:

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

One thought on “Display Data in Listbox based on Combo Box selection”

  1. Sir My Name is Ataullah Khalid. I am a big fan of you. I see your Video ” Transfer data Invoice to worksheet” Sir I follow your Video and its works perfectly. But My boos wants to see the invoice entries to other workbook Sir How Can it is Possible. Please Help me. I am waiting for your Video.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.