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.
Watch the video below before studying the VBA code:
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:
Hello,
Why we can’t download the file?
It’s so hard for you to put for download the file you show us????
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
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.
Hello Mr Dinesh Kumar Takyar ! This is Alberto Cagol (Italy). I have got the same matter. I insert the 2 filters and when I launch the macro appears the error 1004. connected to “Autofilter”
Could you help us?
thank you. Best Regards
Hi Mr. Dinesh Kumar Takyar
I have the same problem as Roy.
I would like to list in list-box populated with filtered data based on selections in the just one text-boxe. Is it possible?
I already tried to use the same code used for the comboxes, with the respective changes, and I can’t find a solution.
I ask you for your help. Thanks
CGaspar
Hi Mr. Dinesh Kumar Takyar
how to use The list-box populated with filtered data based on selection in the just one text-box.
I already tried to use the same code used for the two comboxes, with the respective changes, and I can’t find a solution.
I ask you for your help. Thanks a lot
CarlosGaspar
Hi
This is a great video and apparantly a nice way to select data from a database.
However, i am having some problems.
Run-Time error ‘1004’
Method AutoFilter for class Range failed
Please help to solve this one. I have done an exact copy from your example and using Excel 365.
Thank you so much more for great video understand clear.. i have got your code also i modify adding include 10-combox and adding code update..
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
.List(.ListCount – 1, 4) = cell.Offset(0, 4).Value
.List(.ListCount – 1, 5) = cell.Offset(0, 5).Value
.List(.ListCount – 1, 6) = cell.Offset(0, 6).Value
.List(.ListCount – 1, 7) = cell.Offset(0, 7).Value
.List(.ListCount – 1, 8) = cell.Offset(0, 8).Value
.List(.ListCount – 1, 9) = cell.Offset(0, 9).Value
.List(.ListCount – 1, 10) = cell.Offset(0, 10).Value
.List(.ListCount – 1, 11) = cell.Offset(0, 11).Value
.List(.ListCount – 1, 12) = cell.Offset(0, 12).Value
End With
Next cell
Else
myListBox.Clear ‘ if no match then clear listbox
End If
myListBox.SetFocus
End Sub
“thank you for this code, but i need 65 columns can you help me”