Excel Vba Dependent (Cascaded) Filtering With List Boxes
The columns (column B,C,D) can be filtered as dependent with userform quickly.
When any textbox in the userform is clicked ,the userform extends downwards later listbox that associated to textbox appears .
Data in column are listed as unique and are sorted alphabetic . With text boxes,value can be searched within the listbox with Ado :
Dim s As String, con As Object
Application.ScreenUpdating = False
Set con = CreateObject(“adodb.connection”)
#If VBA7 And Win64 Then
con.Open “Provider=Microsoft.Ace.OLEDB.12.0;Data Source=” & ThisWorkbook.FullName & “;Extended Properties=””Excel 12.0;HDR=No;”””
con.Open “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & ThisWorkbook.FullName & “;Extended Properties=””Excel 8.0;HDR=No;”””
s = “select distinct f2 from [Main$A3:D” & Range(“D” & Rows.Count).End(xlUp).Row & “] where not isnull(f2)”
If TextBox1.Text <> “” Then s = s & ” and f2 like ‘” & VBA.UCase(LCase(TextBox1.Text)) & “%'”
ListBox1.Column = con.Execute(s).getrows
Application.ScreenUpdating = True
Also, the filtering is done with “AutoFilter” method within the worksheet.
Filtering results can be copied to other sheet.