Filter Data into List box using two Combo boxes data as Criteria

How to filter data automatically into Listbox using two combo-boxes data as criteria with VBA.

On a user-form we have two combo-boxes. These combo-boxes are populated with unique data using the ‘dictionaries‘ concept.

The list-box is populated with filtered data based on selections in the two combo-boxes.

The image below shows the filtered data in the list-box.

Filter Data into List box using two Combo-boxes data as Criteria
Filter Data into Listbox using two Combo-boxes data as Criteria

Watch the video below before studying the VBA code:

How to Analyze Excel Data Automatically and Interactively Using Two Combo-Boxes and One List box on a User-Form

Option Explicit
Private Sub cbRegion_Change()

Call FilterData

End Sub

Private Sub cbItem_Change()

Call FilterData

End Sub

Private Sub FilterData()
Dim Region As String
Dim Item_Type As String

Dim myDB As Range

With Me
If .cbRegion.ListIndex < 0 Or .cbItem.ListIndex < 0 Then Exit Sub

Region = .cbRegion.Value
Item_Type = .cbItem.Value

End With

With ActiveWorkbook.Sheets(“MYDATA”)
Set myDB = .Range(“A1:D1”).Resize(.Cells(.Rows.Count, 1).End(xlUp).Row)
End With

With myDB
.AutoFilter ‘remove filters
.AutoFilter Field:=1, Criteria1:=Region ‘ filter data
.SpecialCells(xlCellTypeVisible).AutoFilter Field:=3, Criteria1:=Item_Type ‘ filter data again
Call UpdateListBox(Me.MyListbox, myDB, 1)
.AutoFilter
End With

End Sub

Sub UpdateListBox(MyListbox As MSForms.ListBox, myDB As Range, columnToList As Long)
Dim cell As Range, dataValues As Range

If myDB.SpecialCells(xlCellTypeVisible).Count > myDB.Columns.Count Then
    Set dataValues = myDB.Resize(myDB.Rows.Count + 1)
   MyListbox.Clear ' we clear the listbox before adding new elements
    For Each cell In dataValues.Columns(columnToList).SpecialCells(xlCellTypeVisible)
        With Me.MyListbox
        .AddItem cell.Value
        .List(.ListCount - 1, 1) = cell.Offset(0, 1).Value
        .List(.ListCount - 1, 2) = cell.Offset(0, 2).Value
        .List(.ListCount - 1, 3) = cell.Offset(0, 3).Value
        End With

    Next cell
Else
    MyListbox.Clear ' if no match then clear listbox
End If

MyListbox.SetFocus

End Sub

Private Sub UserForm_Initialize()
Dim dict, key
Dim lastrow As Long
lastrow = Application.WorksheetFunction.CountA(Range(“A:A”))
With Sheets(“MYDATA”).Range(“A2:A” & lastrow)
dict = .Value
End With
With CreateObject(“scripting.dictionary”)
.comparemode = 1 ‘vbTextCompare – case of words doesn’t matter: apple is the same as Apple
For Each key In dict

    If Not .exists(key) Then .Add key, Nothing

Next
If .Count Then Me.cbRegion.List = Application.Transpose(.keys)

End With

With Sheets(“MYDATA”).Range(“C2:C” & lastrow)
dict = .Value
End With
With CreateObject(“scripting.dictionary”)
.comparemode = 1
For Each key In dict
If Not .exists(key) Then .Add key, Nothing
Next
If .Count Then Me.cbItem.List = Application.Transpose(.keys)
End With

End Sub

Further Reading:

Dictionary Object

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

3 thoughts on “Filter Data into List box using two Combo boxes data as Criteria”

  1. Hello,

    Why we can’t download the file?
    It’s so hard for you to put for download the file you show us????

  2. Hello,

    Do you have a tutorial for multiple criteria search for Listbox and update? See the below example. Or can the ComboBox do this?

    TextBox 1 = this will be a word
    TextBox 2 = this will be a date
    ***Add criteria found to ListBox.

    Textbox3 = this will be a date
    ***This will update data in the ListBox

    Thank you,

    Roy

  3. From Portugal, Faro,28 September 2020
    Hi Mr. Dinesh Kumar Takyar
    I have resorted a lot to the topics that you publish, about excel vba, which I thank and appreciate.
    Let me state the following: in the topic “Filter Data into Listbox using two Combo-boxes data as Criteria”, it worked beautifully.
    But if I open a new sheet and insert ” a shape object” as CommandButton, to open the the userform, [Userform1.show],
    it gave me an error. =>(Run-Time error ‘1004’; Application-defined or Object-defined error).
    I’ve tried to use literature, but I don’t really understand it, because there are several theories and procedures, without any result.
    That is why I ask you for your precious help.
    Thank you very much.

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.