Software

10 steps to adding a dependent list control to an Excel sheet

Here's a timesaving way to create a list that changes depending on the user's selections.

List controls seldom come alone. They often come in multiples, where one control determines the items in another. You might have heard this arrangement referred to as cascading controls. By creating a dependency, you can selectively limit choices. In this article, I'll show you how to embed a pair of dependent list controls in 10 easy steps. (If embedded controls aren't right for you, consider a user form.)

1: Create the lists

First, you need the list items. Simply enter the lists in any sheet, as shown in Figure A. I recommend that you keep your lists separate from your other data. We'll use one list to display the categories: Dairy, Produce, and Other. Then, we'll use a second list to display the appropriate items by category. You can also use VBA to populate list controls.

Figure A

Enter the items you'll use to populate the list controls.

2: Apply names to each list

Apply the range name Category to cells A1:C1, as you normally would. When lists are subject to updates, use a dynamic range instead. To create a dynamic range name for the Dairy list, do the following:

  1. Click Define Name in the Defined Names group on the Formulas tab. In Excel 2003, Choose Name from the Insert menu and choose Define.
  2. Name the list Dairy.
  3. Enter the following formula in the Refers To control, as shown in Figure B: =OFFSET(Lists!$A$2,0,0,COUNTA(Lists!$A:$A)-1,1)
  4. Click OK.

Figure B

Enter the formula for the Dairy range.

Repeat steps 1 through 4 to create dynamic lists for the other two lists:

  1. Produce: =OFFSET(Lists!$B$2,0,0,COUNTA(Lists!$B:$B)-1,1)
  2. Other: =OFFSET(Lists!$C$2,0,0,COUNTA(Lists!$C:$C)-1,1)

The formulas are essentially the same; only the column references change. In addition, we're referring to lists on a sheet named Lists.

3: Insert the first control

This example uses two combo box controls. One displays the categories. The second updates according to the category selected in the first. In a new worksheet, insert the first combo box as follows:

  1. Click the Developer tab. In Excel 2003, display the Control Toolbox by choosing Toolbars from the View menu and choosing Control Toolbox.
  2. In the Controls group, click the Insert option and choose Combo Box (ActiveX Control) under ActiveX Controls. In Excel 2003, Combo Box is in the Control Toolbox.
  3. Click the Properties option in the Controls group and name the new control cboCategoryList. In Excel 2003, click Properties in the Control Toolbox.

At this point, you can reset any property, such as font or arrow style. But for now, don't change anything but the control's name.

4: Populate the first control

The next step is to display a list of categories in this first control. We'll do so using the Category range (step 2). Double-click the control and enter the following procedure:

Private Sub Worksheet_Activate()
  'Populate combo box with inventory categories.
  Dim rng As Range
  Dim ws As Worksheet
  Set ws = Worksheets("Lists")
  Me.cboCategoryList.Clear
  For Each rng In ws.Range("Category")
    Me.cboCategoryList.AddItem rng.Value
  Next rng
End Sub

When you're done, return to the worksheet. We used VBA because the control's ListFillRange property requires a columnar list.

5: Use the first control

Before using the control, click Design Mode in the Controls group. Look for Exit Design Mode in the Controls Toolbox in Excel 2003. You can use the first control's list by clicking its drop-down arrow and choosing any of the three categories, as shown in Figure C.

Figure C

Choose a category from the first list.

The event procedure uses a For...Each loop to retrieve list items in Category (Lists!A1:C1). Using the worksheet's Activate event is one of the simplest ways to execute this particular event, but it isn't the only way. Just remember that moving away from the ListControls sheet will reset the control's list and its value.

6: Insert the dependent control

At this point, you have a static control that's displaying three items: Dairy, Produce, and Other. The only way to update this list is to add an item at the sheet level and update the Category range. (You'll see the significance of this in step 10.) Now you're ready to add the dependent list — the one that changes depending on the selection in the first control. Using the instructions in step 3, insert a second combo box and name it cboDependentList.

7: Populate the dependent control

The second control will display three lists based on the dynamic ranges you created in step 2 (Dairy, Produce, and Other). To program it, double-click the embedded control and enter the following procedure:

Private Sub cboCategoryList_Change()
  'Populate dependent combo box with appropriate list items
  'according to selection in cboCategoryList.
  Dim rng As Range
  Dim ws As Worksheet
  Dim str As String
  Set ws = Worksheets("Lists")
  str = cboCategoryList.Value
  Me.cboDependentList.Clear
  On Error Resume Next
  For Each rng In ws.Range(str)
    Me.cboDependentList.AddItem rng.Value
  Next rng
End Sub

When you're done, return to the worksheet.

8: See the dependent results

Both controls are ready to work together, so click Design Mode in the Controls group (or Exit Design Mode in the Controls Toolbox). Then, choose an item, such as Produce, from the first control. Click the second control's drop-down to view the list of produce items (from column B in the Lists sheet), as shown in Figure D.

Figure D

Choosing Produce from the first list populates the second list with a list of produce.
Try it again. This time, choose Dairy from the first list. As you can see in Figure E, the list now displays the dairy items in column A (on the Lists sheet). This second procedure is similar to the first, but the first control's Change event executes this event, which in turn populates the dependent list.

Figure E

Choosing Dairy displays these items.

9: Update a list

You just saw the benefits of a dependent list control. Now let's see what happens when you update one of the lists. Move to the Lists sheet and add Cornbread mix to the Other list, as shown in Figure F.

Figure F

Add a new item to the Other list.

10: See the dynamic results

After adding an item to the Other list, return to the ListControls sheet and choose Other from the first control. Because we used dynamic ranges in step 2, the range automatically adjusts to include the new item, as shown in Figure G.

Figure G

The dependent control shows a new item without any extra work on our part.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks