How to Create a Cascading Combo Box in Excel using VBA

A Cascading combo box gets its list of data (from any source) based on a value selected from another combo box. Usually it is the next combo box to the previous one. Here, in this article I’ll show you how to create a cascading combo box in Excel using VBA or by writing a simple macro.

Cascading Combo Box example using Excel VBA

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.

Related: How to Capture Filter Change Event in Excel using VBA – Trigger a Macro Function on Filter Change

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.

Excel as Database

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.

ActiveX Controls in Excel

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 Sub

I 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 Sub

Well 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.

Hope you like this article and its example. Thanks for reading.

Previous - Quickly Get Filenames from FilePaths in Excel using VBA MacroNext - Excel VBA – Create Folder and Sub Folders in another Folder and Copy Source Files to the Sub Folders



Like this Article? Subscribe now, and get all the latest articles and tips, right in your inbox.

Enter your email id

Delivered by FeedBurner
Tweet this article Google+

Related Posts:

Join our Google Plus Community and be a part of a discussion!