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

1. By Angel Aguila 10 steps to adding a dependent list control to an Excel sheet!!

2. Create the lists

3. 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: Figure B

4. 3: Insert the first control

5. 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.AddItemrng.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.

6. 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. 5: Use the first control Figure C

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

8. 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.ValueMe.cboDependentList.Clear On Error Resume Next For Each rng In ws.Range(str) Me.cboDependentList.AddItemrng.Value Next rng End Sub When you’re done, return to the worksheet.

9. 8: See the dependent results • 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. • 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 Figure E Choosing Dairy displays these items.

10. 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. 9: Update a list Figure F Add a new item to the Other list.

11. . 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. 10: See the dynamic results Figure G

12. The End