Datasheet

Assign Values to
a Form Control
A
fter you add a control to a form, you can assign
it the values you want associated with the
control. For example, if your worksheet contains
a list box, you can assign the list of values that you want
to appear when users access the list box. Some controls
enable you to define a range of valid numeric values for
the control. For example, if you use a spinner, you define
the starting value and the maximum value for the
control. For combo boxes and list boxes, you can place
the options associated with the control in a range of cells.
For example, if you use a combo box, you tell Excel the
list of values used by the control by entering the range of
cells containing the values. The values can be located on
another worksheet or even in another workbook, as long
as Excel can access the workbook when users view the
worksheet that contains the control.
You can link a cell to a control. If you link a cell to a
control, the value associated with a user selection
becomes the value of the linked cell. If you use a combo
box control or list box control, the value in the linked cell
is a number that represents the user’s selection. Excel
assigns the number based on the position of the selected
value in your list. If the list contains the values
Computer, Monitor, and Keyboard, and the user selects
Monitor, the linked cell receives the value 2, because
Monitor is second in the list.
With a control such as a check box, you can tell Excel
whether you want the option to be initially selected or
unselected. Each option — selected or unselected — has a
value associated with it.
1 Right-click the selected control.
A menu appears.
2 Click Format Control.
The Format Object dialog box appears.
3 Click the Control tab.
The available fields are different, depending on the
control type.
This example uses a list box.
4 Drag to select a range, or type the range that lists
the valid values.
5 Click a cell to assign a linked cell.
The value associated with your selection appears in
the linked cell.
6 Type the number of values in your list.
7 Click OK.
1
2
3
5
6
4
7
Assign Values to a Form Control
22
04_132302 ch01.qxp 7/27/07 9:20 PM Page 22