Categories
Excel

Creating Dependent Combo Box Using Option Buttons

Accessing three lists of choices simultaneously generally requires that you use three separate controls, such as three ComboBox control. Instead, you can use a ComboBox in combination with option buttons (also called radio buttons) to have a list change automatically according to which option button you choose.

When working with multiple lists, you can force a list to change by using a combination of option buttons plus a Combo Box. See how this can be done:

  1. In A1:A7, enter the numbers 1 through 7.
  2. In B1:B7, enter the days of the week starting with Monday and ending with Sunday.
  3. In C1:C7, enter the months January through July.
  4. Select Developer » Insert » Option Button. Click the spreadsheet anywhere in three separate spots to place three option buttons on the spreadsheet:
    • Left-click the first option button, select Edit Text, then replace the default text with the word Numbers.
    • Use the same process for the second option button, replace its text with the word Weekdays, and
    • For the third option button, replace its text with the word Months.
  5. Select Developer > Insert > Combo Box. Insert a Combo Box somewhere on the spreadsheet. Using the drag handles, resize the Combo Box to a manageable size and position the option buttons so that they’re directly below the Combo Box.
Inserting Option Buttons and Combo Box

Next, while holding down the Ctrl key, click each option button so that all three are highlighted, then right-click and select Format Control (or Format Object). Specify cell $F$1 as the cell link (make sure it is absolute-use those dollar signs). See the following figure:

Next, in cell E6, enter the following formula:

=ADDRESS(1,$F$1)&":"&ADDRESS(7,$F$1)

The ADDRESS functions we used in this example, 1 represents the first-row number of the lists, while 7 represents the last-row number.

Enter the formula =ADDRESS(1,$F$1)&":"&ADDRESS(7,$F$1) in E6 cell

Now follow these steps to create a new named range:

  1. Select Formulas » Name Manager.
  2. Click New button from the Name Manager dialog box.
  3. Type MyRange in the Name box from the New Name dialog box.
  4. Type this formula =INDIRECT($E$6) in the Refers To field
  5. Click OK.
  6. and Close the Nama Manager dialog box.
Create a named range that refers to =INDIRECT($E$6) formula

Right-click the Combo Box and select Format Control. Make the Input range MyRange and the cell link $G$1, then click OK. See figure:

Setting combo box’s format controls

You should be able to select one of the option buttons, and the list within the Combo Box should automatically reflect which option button you chose.

A multi-list Combo Box controlled by option buttons

Formula and Functions

  1. Extract Number From Cells That Mix of Text and Numbers
  2. Convert Text to Numbers
  3. Using Code and Char Function
  4. Creating Dependent Combo Box Using Option Buttons
  5. Convert Formulas to Values