XL: Can't Link Value of Control to Function

• Microsoft Excel 98 Macintosh Edition
• Microsoft Excel 97 for Windows
• Microsoft Excel for Windows 95, versions 7.0, 7.0a
• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for the Macintosh, versions 5.0, 5.0a

SYMPTOMS

In Microsoft Excel, if you try to link the value of a check box, option button, list box, scroll bar, or spinner control to a function, you will receive one of the following error messages.

   Microsoft Excel 97 and Microsoft Excel 98 Macintosh Edition
   -----------------------------------------------------------

   The text you entered is not a valid reference or defined name.

   Microsoft Excel Version 7.0
   ---------------------------

   Name is not defined.

   Microsoft Excel Version 5.0
   ---------------------------

   Reference is not valid.

CAUSE

You can link the value of a control to a cell in a worksheet by selecting the control and typing the reference to a cell in the formula bar. You can also create the link using the Cell Link box on the Control tab of the Format Object dialog box. When you link the value of a control to a cell, it is designed to be a two-way link, so that a change to the cell value changes the value of the control, and vice versa. Since the control cannot change the value of a function, it is not possible to link the value of a control to a worksheet function or a Visual Basic function.

WORKAROUND

To change the value of a control using a function, link the value of the control to a cell on a worksheet that contains the function by following these steps:

1. Add the desired control to your worksheet or dialog sheet.

2. In a worksheet, select a cell and enter the formula that you want to determine the value of the control.

3. Select the control, in the formula bar, type the reference of the cell in which you entered the formula in step 2. Click OK.

REFERENCES

For more information about the Forms toolbar, click the Search button in Help and type:

    toolbars