Add-Innovation Home

About Add-Innovation

Contact Us

Drop down boxes

There are many ways in Excel to define data in a cell (typing being the most obvious), but sometimes we want that value to be from a predefined list.

Excel does provide the ability to validate the number against values appearing in a range of cells - the validate option. This option requires the number to be manually entered first, issuing a message if the value is incorrect -it's also rather cumbersome to set up.

A better, and altogether more professional looking approach is to use drop down boxes.

The big advantage of this approach is that user simply clicks on the down arrow to enable selection, then scroll down the list to select their required value. They can't select an invalid value, because they're limited to values in the range, the look and feel is the same as Excel and it looks like a professional Excel adaptation, without any programming code or macros.

drop down box in use

To add a drop down box to a spreadsheet, first display the control toolbox (View, Toolbars, Control Toolbox). Next click on the Combo box icon (this can also be located by hovering the cursor over icons until you see the "Combo box" note).

The cursor image will change (to cross-hairs), and you may choose where to drop the control.

When you enabled the control toolbox, the macro toolbar probably appeared at the same time. This should have the design icon button depressed - if not click it. This enables changes to the properties of the control by clicking on it (rather than just showing a blank pull-down list).

Now right click against the tool, and select Properties. A form full of options will appear - scroll down to linked cell. The linked cell is where Excel will place the selection made, and which can be used in your VLOOKUPs.

Next, select List Fill Range, and enter the location of the list of values in your spreadsheet which a user may choose from. The properties form does not allow you to click and drag to define the range, like it does with function boxes, so sometimes ranges can be simpler to define outside of the properties window by entering part of a function (eg. '=SUM('), then clicking and dragging to define the range and finally copying the range displayed too the clipboard - this is especially true for ranges appearing on other sheets.

The definition of the control is now complete, and may be enabled by clicking on the design icon again. You may have noticed that View code was an option presented when you right clicked the control - this would have allowed you to underpin the button with a Visual Basic Macro, but we don't need any of that for the base functionality of the control.

You may also notice that the user selection now appears twice on your form, once on the button itself, and oncee in the linked cell. Sometimes it's preferable to may the linked cell invisible by changing it's colour, or actually moving the control toolbox on top of it.

You can choose whether to print the image of the control or not, by right clicking, choosing Format Control, and ticking the Print image box.