As you can see in the above image, I have two combo boxes. The first has a list of book Categories and the second is the cascading combo box, which is empty when the excel file opens. After you select a category from the first combo box (for example Science), the second combo populates values (a list of science related books) based on the category.
Note: If you have worked with Visual Basic 6, you will find this example simple and easy to understand as I am using ActiveX Data Object (or ADO) classes and methods.
Ok, lets do it.
Create a Database in Excel
I have two sheets in my Excel workbook. The first is sheet1 and the second is Books. I am actually using the second sheet as a database. Therefore, it has a list of books in it. I’ll extract data from the second sheet and populate the combos with the data.
Create a Form in Excel using ActiveX Controls
The sheet1 is a form with two combo boxes. See the first image above. I have also added a button to clear (or reset) the combos. These are ActiveX controls. To add these controls on your sheet, choose the Developer menu on the top, find and click Insert option. Finally, drag and drop the controls that you wish to add on the sheet.
I have named the combo boxes as cmbCategories and cmbBooks respectively. I have named the button control as cmbClear. It will simply clear the selected values in the combo boxes.
Note: I have also added a label control and named it lblPrice, just next to the second combo. This will show the price for the selected book of a particular category. This is optional.
The VBA (Macro) Code to Cascade the Combo Box
Once you have designed the form and added the data to the second sheet (Books), its time to write the VBA code to cascade the combo box. Press Alt+F11 keys to open Visual Basic IDE.
Add a Reference of ActiveX Data Object
Before you write any code, you will need to add a Reference of ActiveX Data Object or ADO. This will provide the necessary classes, properties and methods to connect database objects. In this example, the database is the Excel file itself.
To add reference to your project, click the Tools menu on the top and choose References… option. In the references box, find Microsoft ActiveX Data Objects 6.1 Library (or any current version). Click OK.
From the Project Explorer, double click the ThisWorkbook object and write the below code.
Option Explicit
Private Sub Workbook_Open()
SetConn ' SET CONNECTION.
sQuery = "SELECT DISTINCT Category from [Books$]"
Sheet1.cmbCategory.Clear
If rs.State = adStateOpen Then
rs.Close
End If
rs.CursorLocation = adUseClient
rs.Open sQuery, myConn, adOpenKeyset, adLockOptimistic
If rs.RecordCount > 0 Then
Do While Not rs.EOF
Sheet1.cmbCategory.AddItem rs.Fields(0).Value
rs.MoveNext
Loop
Else
MsgBox "There are no categories in the list.", vbCritical + vbOKOnly
Exit Sub
End If
End SubI wrote this code in ThisWorkbook object, since I want to populate the first combo box (cmbCategories) when you first open the Excel file.
Note: You can populate cmbCategories (the first combo box) using a button click.
Set Database Connection
Next, choose Module1 from the Project Explorer and write a procedure to connect to the database using ADO classes and properties.
Option Explicit Public myConn As New ADODB.Connection Public rs As New ADODB.Recordset Public sQuery As String ' SET A CONNECTION. Sub SetConn() If myConn.State = adStateOpen Then myConn.Close End If Dim sConnString As String sConnString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" & _ "DBQ=" & ActiveWorkbook.Path & Application.PathSeparator & ActiveWorkbook.Name myConn.ConnectionString = sConnString myConn.Open ' OPEN THE CONNECTION. End Sub
Here, I will create and open a database connection using ADODB classes such as Connection and Recordset. If you have worked with Visual Basic 6, this might not be new for you.
Write Cascade Procedure in “Sheet1”
Finally, double click Sheet1 object in the Project Explorer and write these procedures in it.
Option Explicit
Private Sub cmbCategory_Change()
' THE CHANGE EVENT OF THE FIRST COMBO TO POPULATE THE SECOND COMBO BOX.
If Trim(cmbCategory.Text) <> "" Then
SetConn ' SET THE CONNECTION TO THE DATABASE.
' SQL QUERY TO FETCH BOOKS BASED ON THE SELECTED CATEGORY.
sQuery = "SELECT BookName FROM [Books$] WHERE " & _
"Category = '" & cmbCategory.Text & "' " & _
"ORDER BY BookName"
cmbBooks.Clear ' CLEAR THE BOOK COMBOBOX.
lblPrice.Caption = ""
If rs.State = adStateOpen Then
rs.Close
End If
rs.CursorLocation = adUseClient
' POPULATE CASCADING COMBO BOX WITH VALUES.
rs.Open sQuery, myConn, adOpenKeyset, adLockOptimistic
If rs.RecordCount > 0 Then
Do While Not rs.EOF
cmbBooks.AddItem rs.Fields(0).Value
rs.MoveNext
Loop
End If
End If
End Sub
' SHOW THE PRICE OF THE SELECTED BOOK.
Private Sub cmbBooks_Change()
If Trim(cmbBooks.Text) <> "" Then
SetConn
' GET THE PRICE FOR THE SELECTED BOOK FROM SHEET "Books".
sQuery = "SELECT Price FROM [Books$] WHERE " & _
"Category = '" & cmbCategory.Text & "' AND " & _
"BookName = '" & cmbBooks.Text & "' "
lblPrice.Caption = ""
If rs.State = adStateOpen Then
rs.Close
End If
rs.CursorLocation = adUseClient
rs.Open sQuery, myConn, adOpenKeyset, adLockOptimistic
' SHOW THE PRICE, IF AVAILABLE.
If rs.RecordCount > 0 Then
lblPrice.Caption = "(Price $" & Format(rs.Fields(0).Value, "0.00") & ")"
End If
End If
End Sub
Private Sub cmbClear_Click()
cmbCategory.Text = ""
cmbBooks.Text = ""
lblPrice.Caption = ""
End SubWell that’s it.
Save this Excel file as Macro-Enabled workbook. Open the file and you will see a list of Distinct categories in the first combo box. Choose a category and it will automatically populate related data (list of books) in the second combo box. It will extract data from the second sheet named Books in the same workbook. Finally, choose a book from the list and it will show the price of the book.
Along with cascading combo box, I have actually showed you how to create a simple database in Excel and extract the data using Microsoft ADO classes and properties.