To link a control to a worksheet cellSome controls can be linked to one or more worksheet cells. If the cell value changes, the control value changes, and vice versa. For example, if you've linked an option button to a worksheet cell, the cell value is True as long as the option button is turned on. If you change the cell value to False, the option button is turned off. You can use linked cells to set initial conditions for controls and to create worksheets that respond immediately to user input.
Because changing the state of the control changes the value in the linked cell, any formulas referencing the linked cell are recalculated when you change the control. You might use this feature to implement a loan calculation worksheet for which the results depend on the interest rate. Linking a spinner to the interest rate cell would allow you to change the interest rate simply by clicking the spinner. This way, the loan payment would automatically recalculate when the interest rate changes.
Note
If you have the Automatic Calculation option set, changing a linked control will cause the entire worksheet to recalculate. To turn off Automatic Calculation, click Options on the Tools menu, click the Calculation tab, and then click Manual under Calculation.
Worksheet cells can be linked to check boxes, list boxes, drop-down list boxes, option buttons, scroll bars, and spinners. The link is a property of the control, not of the linked cell.
You can link the same cell to more than one control, but the control can be linked to only one cell. You can reference the linked cell in any other cell where you want to use the linked value.
You can link a control on a dialog sheet to a worksheet cell, but the link is always active, so changing the state of the control immediately changes the value appearing in the linked cell. This means that changes appear on the worksheet even if the user clicks Cancel to close the dialog box. This behavior is usually not desired. As a rule, you'd like to have the changes appear only when the user clicks OK or presses ENTER to dismiss the dialog box. For this reason, it's usually a better idea to use a procedure to transfer information from a dialog box to the cells on a worksheet and to run that procedure only when the user clicks OK. For more information, see "Getting Information from a Dialog Box" later in this chapter.
You can link a control to a cell and assign a procedure to the same control. In this case, the link is updated before the procedure runs. For more information, see the preceding section, "Assigning Code to Controls."
You can specify the cell link reference for a control on the Control tab in the Format Object dialog box.
To link a control to a worksheet cell
1. Select the control.
2. On the Format menu, click Object, and then click the Control tab.
3. In the Cell Link box, type the name or reference of the linked cell.
You can also click the cell to enter its reference in the box.
4. Enter any additional information that pertains to the linked control, as described in the following sections. For example, use the Input Range box to specify the cells you want to use to fill a list box.
Note
You can also establish a link by selecting the control on the worksheet or dialog sheet and then typing the cell reference in the formula bar.
The LinkedCell property, which corresponds to the Cell Link box in the Format Object dialog box, sets the cell link reference for a control. The following example links the check box named "Check Box 3" on the worksheet named "sheet1" to cell A5 on the same worksheet.
Worksheets("sheet1").CheckBoxes("Check Box 3").LinkedCell = "sheet1!a5"
List boxes and drop-down list boxes also use the ListFillRange property, which corresponds to the Input Range box in the Format Object dialog box. This property sets the worksheet range used to fill the list box. The following example fills the list box named "List Box 2" on the sheet named "sheet1" with the contents of cells A5:A10 on the same worksheet. Changes to the worksheet cells appear in the list box.
Worksheets("sheet1").ListBoxes("List Box 2"). _
ListFillRange = "sheet1!a5:a10"
For a check box, the value in the linked cell reflects the state of the check box. The check box can appear checked, unchecked, or grayed. These values correspond to the linked cell values True, False, and #N/A. Typing one of these values in the linked cell changes the state of the check box. Manually changing the check box changes the value in the linked cell.
For grouped option buttons that are all linked to the same cell, the value in the linked cell shows the ordinal number of the option button that's turned on. For example, if an option button group contains four buttons and the third button is turned on, the value in the linked cell is 3. Changing the value in the linked cell to 1 turns on the first option button in the group. A linked cell value smaller than 1 or larger than the total number of buttons in the group (four, in this example) causes all buttons in the group to be turned off.
For a list box in which you can select only one item at a time (called a single-select list box), the value in the linked cell shows the ordinal number of the selected item in the list box (item one is at the top of the list box). For a multipleselection list box (a list box in which more than one item can be selected at a time), the value in the linked cell has no meaning.
You can also specify a range of cells on a worksheet that contains the list of items to appear in the list box, using the Input Range box on the Control tab in the Format Object dialog box, or using the ListFillRange property.
Formulas that depend on the linked cell are recalculated whenever the selected item changes ; this is useful for creating what-if models in which the user can watch results change while moving the selection up and down in the list box.
For a scroll bar, the value in the linked cell specifies the position of the scroll box in the scroll bar. The Format Object dialog box allows you to specify minimum and maximum values for the scoll bar and to specify the amount by which the position value changes when the user clicks the arrows or the scroll bar. You can also specify these values using the Min, Max, SmallChange, and LargeChange properties.
Changing the value in the linked cell changes the position of the scroll box. Changing the value in the linked cell to a number smaller than the minimum value moves the scroll box to the minimum position. Likewise, setting the value in the linked cell to a number larger than the maximum value moves the scroll box to the maximum position.
For a spinner, the value in the linked cell represents the current "value" of the spinner. Unlike a scroll bar, a spinner has no visible position indicator (such as a scroll box). However, you set a minimum and maximum value for a spinner just as you do for a scroll bar, and the spinner value is incremented when you click the up arrow and decremented when you click the down arrow. Spinners support only the Max, Min, and SmallChange properties.