Excel Programming for Non-Programmers Series #1: Populating Cascading Combo Boxes Part2

This is the second in a two-part series on populating Combo Boxes using Advanced Filter and VBA in Excel.  Click here to read the first part, Populating Combo Boxes Part1 – Advanced Filter. We will start with the Excel file that you modified in Part 1.

The Excel file includes the data set, and some code that we wrote in the first part of the series.  In this part, we’ll learn how to use the Advanced Filter code to populate combo boxes.

The intent of this post is not to teach the concepts of programming, but rather to provide a framework that can be used in your projects with modification.  Reviewing the code, associated comments, and the contents of this post should provide enough information for you to figure out how to modify the code to meet your needs.

Create the Combo Boxes

Before the Combo Boxes can be created, you’ll need enable the Developer tab in the Ribbon (Excel 2007 and 2010), or show the developer toolbar in Excel 2003. Here are the steps for Excel 2007:

• Click the Microsoft Office Button and then click Excel Options
• Click Popular, and then select the Show Developer tab in the Ribbon check box.

Use your knowledge of Advanced Filters to create a unique list for Category in column E of the Data worksheet.

Do the following to create the Category and Make combo boxes:

• Create a new worksheet in the workbook, and name it Report
• Select the Report worksheet
• Click the Developer tab
• Click the Insert menu
• Choose the Combo Box ActiveX control (note: do not select the form control)

• Draw two combo boxes, one will be used to select the category, and the other will be used to select from the makes included in that category
• Create headings for Category, Make and Model, as shown below

• On the Developer tab, click the Design Mode button

• Right-click the Category combo box and choose Properties
• Enter cboCategory in the (Name) field
• Right-click the Make combo box and choose Properties
• Enter cboMake in the (Name) field

• Enter ‘Data’!E4:E8 in the ListFillRange field
• Click the Design Mode button to leave the design mode

Now you have the start of a report, with the Category combo box populated using the unique list of categories on the Data tab. The Make combo box will be populated using VBA code. We will refer to the combo boxes using the names you entered in the Properties dialog box: cboCategory and cboMake.

The Strategy

Let’s take a moment to better understand how the report will work.  The user will interact with the Report tab and will begin by selecting a Category. The Make combo box will populate with all makes available in that category. The system will watch for a change in Category, and then populate the Make combo box based on the selected category value.

Similarly, when the Make is changed, the available models will be populated in column D of the Report worksheet.  We’ll use the Change event in VBA to carry out this strategy.

“Category” Change Code

When the user changes the Category selection in the report, the Make combo box is populated based on the selection.

Press ALT + F11 to bring up the Visual Basic Editor. In the Project pane, at the top left, double-click the Report sheet to bring up the code window for that sheet.

Paste the following code into the code window. Take some time to read the comments and the code statements to understand how this solution works.

'This subroutine will spring into action when the user changes the
'Category combo box. It clears the Make combo box,
'updates the Advanced Filter criteria based on the user's category selection,
'filters the data to reflect makes linked to the selected category,
'and populates the Make combo box with the filtered data.

Private Sub cboCategory_Change()
    'Creates a string variable, Category, that stores the value of
    'the Category selected by the user.
    Dim Category As String
    
    'Sets the Category variable to the category value selected by the user
    Category = cboCategory.Value
    
    'Clears the filter criteria (row 2 in the Data worksheet).
    Worksheets("Data").Range("A2:C2").Clear

    'Sets the filter criteria based on the new selection.
    ‘Don't worry if the code looks complicated to you, just focus on the key parts:
    ‘The range is where the formula will be written
    ‘(i.e. in cell A2 on the Data worksheet)
    ‘and the Category variable 
    Worksheets("Data").Range("A2").Formula = _
        "=" & Chr(34) & "=" & Chr(34) & "&" & Chr(34) & Category & Chr(34)
    
     ‘Clears the range where the results will be written
     Worksheets("Data").Range("G3:H26").Clear
    
    'Uses the Advanced Filter to create a unique set of make values that
    'are linked to the selected category.  Read the first part of the series,
    '"Populating Cascading Combo Boxes Part1" for details
    Worksheets("Data").Range("$A$3:$B$26").AdvancedFilter _
        Action:=xlFilterCopy, _
        criteriarange:=Worksheets("Data").Range("A1:B2"), _
        CopyToRange:=Worksheets("Data").Range("G3"), _
        Unique:=True
    
    'Clears the contents of the Make combo box, and sets the selected value
    'to nothing.  This prepares it to be populated by the newly filtered
    'set of values for make.
    cboMake.Clear
    cboMake.Value = ""
    
    'Uses a loop to step through each cell in the new make list
    'and adds each make to the combo box.
    'If a value is blank, it is not added.
    For Each cell In Worksheets("Data").Range("H4:H26")
        If IsEmpty(cell) = False Then
            cboMake.AddItem cell.Value
        End If
    Next cell
End Sub

“Make” Change Code

When the user changes the Category selection in the report, the models are populated based on the selection.

Enter the following code after the “Category” change code.  Again, take some time to read the comments and the code statements to understand how this solution works.

'This subroutine will spring into action when the user changes the
'Make combo box.
'It updates the Advanced Filter criteria based on the user's
'category and make selections,
'filters the data to reflect models linked to the selected category and make,
'and populates the D column in the report with the resulting list of models.

Private Sub cboMake_Change()
    'Creates string variables, Category and Make, to store the values of
    'the Category and Make selected by the user.
    Dim Category As String
    Dim Make As String
    
    'Sets the Category variable
    'and Make variable to the values selected by the user
    Category = Worksheets("Report").cboCategory.Value
    Make = Worksheets("Report").cboMake.Value
    
    'Sets the filter criteria.
    'Read the post for details on this formula
    Worksheets("Data").Range("A2").Formula = _
        "=" & Chr(34) & "=" & Chr(34) & "&" & Chr(34) & Category & Chr(34)
    
    Worksheets("Data").Range("B2").Formula = _
        "=" & Chr(34) & "=" & Chr(34) & "&" & Chr(34) & Make & Chr(34)
        
    'Clears the previous list of models
    Worksheets("Data").Range("J3:L26").Clear
     
    'Uses the Advanced Filter to create a unique set of model values that
    'are linked to the selected Category.  Read the first part of the series,
    '"Populating Cascading Combo Boxes Part1" for details
    Worksheets("Data").Range("$A$3:$C$26").AdvancedFilter _
        Action:=xlFilterCopy, _
        criteriarange:=Worksheets("Data").Range("A1:C2"), _
        CopyToRange:=Worksheets("Data").Range("J3"), _
        Unique:=True
        
    'Copies the resulting list of models from the filtered list in the Data
    'worksheet to the Report worksheet (column D)
    Worksheets("Data").Range("L4:L26").Copy
    Sheets("Report").Select
    Range("D2").Select
    ActiveSheet.Paste
    Range("D2").Select
End Sub

Run the Report

You’ve completed the report, so try it out! Close the Visual Basic Editor window and navigate to the Report worksheet. Choose a category (e.g. “Sports”) from the Category combo box. The Make combo box populates. Choose a make from the Make combo box (e.g. Porsche). Now the model list populates.