As a knowledge worker, it is not uncommon to find yourself at a client with the tools that you are provided, and not the tools that you might want. You will, however, always find Microsoft Excel. For this reason, Excel is often used beyond its intended purpose; as a forms tool, a reporting tool, a database or an application front-end.
Although your first course of action should probably be to obtain the right tool, sometimes you just have to "make it work." This is the first in a series of Excel tips and tricks for non-programmers that will help you to understand and perform common programming tasks.
It is a common requirement to populate cascading Combo Boxes in Excel. Imagine selecting a state in the first box, with the second box populating with valid cities in that state. This example is too easy, however, since the data will probably remain the same in the foreseeable future.
For our more realistic example, we'll imagine an excel export, on a monthly basis, indicating which cars are available for purchase. There are three columns in this report: Category, Make and Model. Our job is to create an interactive report that allows the user to select a category, then a make, and then view a list of available models.
In this post, I will introduce you to the Advanced Filter. In Part 2, I'll show you how to use the Advanced Filter to populate the Combo Boxes in code.
Although this solution is designed to work with larger data sets, we'll use with a fairly simple example. Download the sample file attached to this posting. This is what it looks like:
Using Advanced Filter
Insert two rows above the data set. They will be used by the Advanced Filter. Type the headers again at the top (row 1). The result should look like this:
Let's see how the Advanced Filter works by searching for sports cars. First enter the criteria for filtering in cell A2. Type ="=Sports". =Sports should appear in the cell.
Activate the filter by clicking Data > Advanced.
- Choose Filter the list, in-place
- Enter or select $A$3:$C$26 as the List range
- Enter or select $A$1:$C$2 as the Criteria range
- Click OK
There should be five sports cars listed.
Listing Unique Values
The Advanced Filter can also be used to list a unique set of values, which is instrumental in populating combo boxes.
Clear the filter by clicking Data> Clear in MS Excel 2007, or Data > Filter > Show All in MS Excel 2003.
Delete the filter criteria in cell A2.
Look at the data set again. Note that there are five unique values for Category: Hybrid, Sedan, Sports, SUV and Truck. That list should be the contents of the first Combo Box, and the Advanced Filter can be used to create this unique list of values. Click Data > Advanced. The Advanced Filter dialog box appears.
- Choose Copy to another location
- Enter or select $A$3:$A$26 as the List range
- Enter or select $A$1:$A$2 as the Criteria range
- Enter or select $E$3 as the Copy to range
- Check the Unique records only box
- Click OK
Note that Cells E3 through E8 contain a unique list of values for Category. Delete column E. We'll do the same filtering using VBA code.
Advanced Filtering using VBA Code
In order for the Advanced Filter to be useful in populating cascading Combo Boxes, we'll need to manipulate it using VBA. Every time a new selection is made, it will trigger an update to the lower-level Combo Box(es).
If this is your first time using VBA, do not fear! Jump right in by pressing ALT + F11 to bring up the Visual Basic Editor.
In the Project pane, at the top left, double-click Sheet1(Data) to bring up the code window for that sheet.
Sub FilterCategory() in the blank code window for Sheet1 and press Enter. The VBA Editor automatically creates the End Sub statement. Your code should look like this:
Sub FilterCategory() End Sub
Type the Advanced Filter code, so that your subroutine looks like this:
Sub FilterCategory() Worksheets("Data").Range("$A$3:$A$26").AdvancedFilter _ Action:=xlFilterCopy, _ CopyToRange:=Range("F3"), _ Unique:=True End Sub
Note that the code has the same parameters as the Advanced Filter dialog box. When called, this applies an Advanced Filter to the Category column (column A) on the "Data" worksheet. Because ‘Unique' is set to True, it places a unique set of values into a list, starting at cell F3 on the same worksheet.
Press F5 to run your code. Check the results on the Data sheet. It should look like this:
In the next posting, I will introduce the Combo Boxes and demonstrate how to work with them using VBA and the Advanced Filter concepts covered here.