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
    Me.ListBox1.Clear
    DoEvents
   
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;"""
   #Else
    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