Home > Excel Tutorial > Excel Vba Dependent (Cascaded) Filtering With List Boxes

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;”””
    #End If
    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.

Example Workbook Can Be Downloaded Here

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: