Excel VBA Cascading Dependent Drop Down Lists

Excel Filter With Dependent Combo Boxes And Pull Filtered Results Into Listbox


excel vba dependent drop down list

          We have used 4 dependent combo boxes (dependent drop down lists)  in this study again. We filtered the data on the sheet with combo boxes,and pulled filtered results into listbox. If desired ,the filtered data can be copied with button to other pages.

In the drop-down lists, the unique values  are sorted in  ascending order. We used ADO Connection to do all these operations quickly.

excel cascading dependent drop down list

✔️ To be able to faster the filtering process, we used ADO  (ActiveX Data Objects). ADO is a subset of the Visual Basic programming language specifically designed for communicating with databases.

✔️ To avoid compatibility problems between 32bit and 64bit ,we organized codes as follows :

excel dependent drop down list


✔️ When the cursor hovers on combo boxes, they are opened as automatically. For this ,following codes :
excel vba dependent combobox

✔️ Codes that allow conditional cascading dependent drop-down lists to run:
Private Sub Userform_initialize()
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
Call Combo("")
End Sub

Private Sub ComboBox1_Change()
If Not ComboBox1.Text = "" Then
    Call Listbox
    Call Combo(sql)
End If
End Sub

Private Sub ComboBox2_Change()
If Not ComboBox2.Text = "" Then
    Call Listbox
    Call Combo(sql)
End If
End Sub

Private Sub ComboBox3_Change()
If Not ComboBox3.Text = "" Then
    Call Listbox
    Call Combo(sql)
End If
End Sub
Private Sub ComboBox4_Change()
If Not ComboBox4.Text = "" Then
    Call Listbox
    Call Combo(sql)
End If
End Sub

Private Sub ComboBox3_Change()
If Not ComboBox3.Text = "" Then
    Call Listbox
    Call Combo(sql)
End If
End Sub

Private Sub ComboBox4_Change()
If Not ComboBox4.Text = "" Then
    Call Listbox
    Call Combo(sql)
End If
End Sub

Our template is ready for use .


excel vba dependent drop down list

2 comments:

  1. Great job for publishing such a beneficial web site. Your web log isn’t only useful but it is additionally really creative too. There tend to be not many people who can certainly write not so simple posts that artistically. Continue the nice writing
    vba course london

    ReplyDelete
  2. Hi, great site, great examples.
    This one I want to use for something, but I have one problem. In my data base, two column are with numbers and I get error message "2147217913 Data type mismatch in criteria expression". What I need to change to avoid this error?

    ReplyDelete