Form Controls: Combo Box, Buttons and Check Boxes

The problem

A drop-down built with data validation is quick, but the cell that holds it is also the cell that holds the data. A scenario chosen in F2 lives in F2. Every sheet that depends on it must point at that one cell, and the reader must go there to change it. On a dashboard, that is awkward.

Form controls take another approach. A combo box, a check box or a scroll bar is a graphic object that floats above the grid, independent of the cells. It stores nothing itself. It writes its state into a separate cell, the linked cell, and the model reads that cell. The linked cell can sit anywhere, usually in a parameters block or a sheet of control variables, and works as a global variable of the workbook. The control can be placed beside a chart, copied to other sheets and still drive the same cell.

The examples use the companion file W06_Form_Controls.xlsx. Controls cannot be generated by the script that builds the file, so it contains everything but the controls: the scenario table and the linked cells in Parameters, the formulas in Model and a step-by-step guide in How to add the controls. You insert the controls, which is the best way to learn them.

Form controls and ActiveX controls

The Insert button on the Developer tab offers two sets. Form controls are the older and simpler set. They work without VBA, they can run a macro, and they reference cell data. ActiveX controls have more properties and respond to events through code, and they can also be used on UserForms. Microsoft gives a quick way to tell them apart: right-click the control, and if the menu shows Properties it is an ActiveX control, while Assign Macro means a Form control. For models, Form controls are the right choice.

The Developer tab is hidden by default. Show it with File > Options > Customize Ribbon, ticking Developer.

The combo box

The Parameters sheet has three scenarios in A2:E4 (Base, Upside and Downside), each with a list price, units per month, rebate rate and the cost of an access program. The combo box chooses among them. The steps, from Microsoft's page:

1. Select Developer > Insert and, under Form Controls, choose Combo Box.
2. Click on the Model sheet, next to cell B1, and drag to draw the control.
3. Right-click it and choose Format Control, then the Control tab.
4. In Input range, enter the cell range of the list: Parameters!$A$2:$A$4.
5. In Cell link, enter the cell that will receive the choice: Parameters!$H$2.
6. Set the Drop-down lines, here 3. If the number is smaller than the list, a scroll bar appears.

Write the sheet name in both references, as above. Without it, a control copied to another sheet can end up pointing at that sheet's cells. The input range must be a range of cells or a name. It cannot be typed as text.

The linked cell does not receive the text of the choice. It receives its position in the list. Select Upside, the second item, and H2 shows 2. This looks like a nuisance and is an advantage. A position is exactly what INDEX, XLOOKUP and MATCH expect, and one position can retrieve a full set of parameters from a table:

=INDEX(ScenarioList,SafeIdx)                            returns Upside
=INDEX(Parameters!$B$2:$B$4,SafeIdx)*(1+(PriceSteps-PriceMid)*StepSize)

Nothing in the model depends on where the control sits. ScenarioIdx, UseRebate and the other linked cells are named, as described in the article on names, so every formula says what it reads.

A guard for the linked cell

A control can leave its cell in a state the model did not plan for. If someone deletes the content of H2, or sets it to 0, INDEX with a position of 0 returns the whole column instead of a single value. The file prevents it in two steps. Parameters!H10 calculates a safe position, =MAX(1,MIN(ScenarioIdx,ROWS(ScenarioList))), which keeps the value between 1 and the number of scenarios, and every INDEX in the model reads that cell. H12 shows "OK" while the link is valid and a warning when it is not. The guard costs one cell, and saves a puzzling failure.

Check boxes

A check box has two states, so its linked cell receives TRUE or FALSE. It fits a yes/no choice: include an optional cost, switch on a sensitivity. In the file, the first check box links to H3 (UseRebate) and the second to H4 (UseProgram). The formulas use the value directly in IF:

=IF(UseRebate,INDEX(Parameters!$D$2:$D$4,SafeIdx),0)
=IF(UseProgram,INDEX(Parameters!$E$2:$E$4,SafeIdx),0)

For the Base scenario, annual net sales are US$65,108,736 and, after the access program, a contribution of 64,858,736. Untick the rebate and net sales rise to 73,987,200, because the 12% is no longer deducted. Upside gives 77,686,560 of net sales and 77,436,560 after the program, and Downside gives 49,510,944 and 49,260,944. The check boxes and the combo box act together, and each combination is one click away.

Several check boxes are independent of each other. When only one of a group of options can be on, the control is the option button, which is placed inside a group box and shares one linked cell.

List box, scroll bar and spin button

The List Box works like the combo box, but shows all the alternatives at once, which suits a short list. The Scroll Bar and the Spin Button change a whole number in the linked cell by a fixed step. With a list of 100 hotels, a scroll bar between 1 and 100 moves through them. In the file, the scroll bar goes from 0 to 20 in steps of 1, linked to H5. It cannot go below zero, so H7 holds the midpoint of 10, and each step above or below changes the list price by the 0.5% in H6. At 15 the price rises 2.5%, to 1,316.61, and Base net sales become 66,736,454. The step and the midpoint are in cells, in grey, and not in the formula.

A Button runs a macro. Without VBA it does nothing, so it is left out of this exercise.

Document the controls

A model with controls spreads decisions across the sheets, and the person who inherits it needs to know where. The linked cells are in one block on Parameters, with a column that says which control writes each one. That is the minimum documentation. A reader who wants to know why Upside appears can find the cell, the control and the formulas that read it.

Comparing the two drop-downs

Common mistakes

• Pointing the linked cell to a cell that already holds something. The control overwrites it.
• Leaving the sheet name out of the input range or the cell link.
• Reading the linked cell as text. It holds a position, or TRUE or FALSE.
• Using INDEX with the raw linked cell and no guard against 0.
• Mixing up Form controls and ActiveX controls and looking for macros where there are none.
• Typing the list of the combo box as text. It must be a range or a name.
• Spreading linked cells over many sheets without a note of what writes them.
• Hiding the parameters sheet and forgetting to document it.

For further reading

• Overview of forms, Form controls, and ActiveX controls on a worksheet - Microsoft Support
• Add a list box or combo box to a worksheet in Excel - Microsoft Support