Saturday, September 10, 2016

Excel Vba Dependent (Cascaded) Filtering With Ado

            The columns (based on column B,C,D) can be filtered as dependent with userform quickly.

The userform contains 3 textbox and 3 listbox. When any textbox 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 Connection :
 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)) & "%'"
On Error GoTo hata
    ListBox1.Column = con.Execute(s).getrows
Application.ScreenUpdating = True

In addition, the filtering is done with "AutoFilter" method within the worksheet.

Filtering results can be copied to other sheet.

No comments:

Post a Comment