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.

16 comments
MommaTRex
MommaTRex

I think I figured out why most people are having problems! I think one way to avoid the problem in the first place is to add this step between Step 1 and Step 2: "Step 1A: After typing in the lists, but before naming them in Step 2, rename the sheet "Lists". Now save the file with any name you like as long as it is a macro-enabled file type (.xlsm file for Excel 2007)." But I think you can fix one you already created by checking the names of Dairy, Produce, and Other and making sure they are correct. To test if the named range Dairy is working, type the following formula into any blank cell in column D: =VLOOKUP("Milk",Dairy,1,FALSE) You should get the result "Milk". If you get an error, your defined name for Dairy is probably messed up. Go to the Name Manager and make sure it looks like it should in Step 2. P.S. Don't forget that sometimes to make the first combo box work, you need to click on another tab and then back again.

MommaTRex
MommaTRex

In Step 7, the macro should be attached to the FIRST combo box. Read the instruction "double-click the embedded control and enter the following procedure:" as "click on the FIRST box you created and then click on the View Code button on the Developer ribbon (Excel 2007). The first and last lines of the macro will be filled in for you. Copy and paste the other lines of the code, except the first and last. You might also need break the lines with a stroke of the Enter key as needed."

tnbrla
tnbrla

I was finally able to get the second control to display the list of choices when I click the drop down arrow. However, when I select one of the items in the drop down list, that item will not populate the control box. Instead, it remains empty. Help?

tnbrla
tnbrla

I get the same result as Kuthumi (above). I can get the first control to populate but not the second. I tried starting over completely with the same result. I'm not getting any error messages -- just no items in the 2nd list. I think this could be quite useful for my company. Thoughts? Thanks

kuthumi_9
kuthumi_9

At first, neither of the controls displayed anything. In step 1, there was no explicit instruction to name your worksheet, so I named the datasheet as "Lists" to match the reference provided later in the article. To populate the first control, take the Step 2 text "Apply the range name Category to cells A1:C1, as you normally would" as an action step, and not just some text commentary: in the Formulas tab (Excel 2010), click on Name Manager, and set the following: "Name:" = Category, "Scope: " = Lists, and "Refers to:" as =Lists!$A$1:$C$1. For me, that populated the first control. Populating the second control is still a mystery, and I'd appreciate an update.

ilyasm2003
ilyasm2003

I tried to follow the instructions but sadly even the first combo isn't working.

antonia.creese
antonia.creese

Agree with other post...I followed all the instructions to the letter and even the first combo doesn't work...disappointing

carl2942
carl2942

Is the 10 steps to adding a dependent list control to an Excel sheet available for a file download? Thank you in advance

MommaTRex
MommaTRex

I think the above worked just because I was fooling around with it. But see my next comment about defined names!

MommaTRex
MommaTRex

Check the defined names for Dairy, Produce, and Other. (see my latest post)

MommaTRex
MommaTRex

Check the defined names for Dairy, Produce, and Other. (see my latest post)

ssharkins
ssharkins

I suppose I take the sheet references for granted. I'm sorry for any confusion that caused you.

ssharkins
ssharkins

Tell me what's happening or not happening and we'll figure it out.

kuthumi_9
kuthumi_9

Thank you for the response. Since this topic is very timely for a problem I'm working on, I'd really appreciate the solution for populating the dependent second control. Thank you for your time with this.

antonia.creese
antonia.creese

I am using excel 2003. after an internet search I was able to populate a list validation in a cell with the static and the dynamic list but the indirect function displays blanks and the combo box shows blanks.

MommaTRex
MommaTRex

Check the defined names for Dairy, Produce, and Other. (see my latest post)