Home > Excel Tutorial > Excel Vba Dependent Drop Down Lists With ADO Connection

Excel Vba Dependent Drop Down Lists With ADO Connection

        We used the dependent combo boxes  in this example. 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.

To be able to faster the filtering process, we used ADO  (ActiveX Data Objects) in this template. 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 :

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

When the cursor hovers on combo boxes, they are opened as automatically. For this ,following codes :

Private Sub ComboBox1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Me.ComboBox1.DropDown
End Sub

Private Sub ComboBox2_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Me.ComboBox2.DropDown
End Sub

dependent drop down lists

Download Example File

  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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: