Drop-down list box

Instead of a reference, you can type a numeric value or a formula in the Refers To box. You can use named formulas to simplify long worksheet formulas by assigning names to parts of the formula. See "Returning an Array of Unique Entries in a List" in Chapter 4 or "A Drop-down List Box on a Worksheet" in Chapter 8 for examples of this technique. 

For an example of using OFFSET, see "A Drop-down List Box on a Worksheet" in Chapter 8. 

The controls that can be installed on a worksheet to return a value to a worksheet cell are an option button, a check box, a list box, a drop-down list box (sometimes called a combo box), a scroll bar and a spinner. In addition, you can install a group box, which does not return a value but is used to group related controls, usually option buttons. 

A drop-down list box (combo box) displays a list of items. Initially only one item is visible in the window the user presses the drop-down button to display the rest of the list, then chooses an item. 

Combo Box Input Range a reference to a range of cells whose values will be displayed in the drop-down list box. 

Figure 8-3 shows a drop-down list box that is used to display a subset of a list. This drop-down list box displays, from a list of names and telephone... 

The advantage of using the drop-down list box rather than a simple list box is that the box automatically sizes to fit the number of values displayed. 

Figure 8-3. A drop-down list box displaying a subset of a list. The cell link is cell 12.

This example illustrates the use of option buttons in combination with the drop-down list box of the preceding example. The option buttons are used to specify whether to display the address or telephone number corresponding to the selected name. 

To save a workbook as an Add-In, choose Save As... from the File menu. Choose Microsoft Excel Add-In from the Save File As Type drop-down list box, then press OK. In Excel for Windows, Add-In macros are automatically given the filename extension. xla. 

Excel Tip. A toolbar that contains a drop-down list box, such as the Style box, cannot be placed in a vertical position in the left or right toolbar docks. 

DropDown.xls illustrates two drop-down list boxes that display selected names, addresses and telephone numbers from a database. 

---