XL97: Change Event for ActiveX Control Runs Unexpectedly

ID: Q169885

The information in this article applies to:

Microsoft Excel 97 for Windows

SYMPTOMS

A change event macro that is assigned to an ActiveX control on a worksheet runs when the worksheet is recalculated instead of running when you change the control value.

CAUSE

This problem occurs when you assign the ListFillRange property of the control to either of the following:

A defined name that refers to a formula or cell containing a volatile function.

A cell that contains a formula containing a volatile function.

NOTE: A volatile function must be recalculated whenever calculation occurs in any cells on the worksheet. A nonvolatile function is recalculated only when the input variables change.

RESOLUTION

To prevent the change event from running unexpectedly, do not assign its ListFillRange property to a defined name or to a cell that contains a function that is calculated whenever the worksheet changes (a volatile function).

STATUS

This behavior is by design of Microsoft Excel 97.

MORE INFORMATION

In Microsoft Excel 97, you can insert controls, for example a list box, drop down, and edit box, into a worksheet. The ListFillRange property of the control can be used to populate the control with information from the worksheet. However, when you assign a macro to the change event for the control and the data that populates the control contains a volatile function, the change event macro may run whenever the worksheet is recalculated.

Functions that are recalculated whenever the worksheet changes are volatile functions. The following functions are volatile:

AREAS()
INDEX()
OFFSET()
CELL()
INDIRECT()
ROWS()
COLUMNS()
NOW()
RAND()

For additional information volatile functions, please see the following article in the Microsoft Knowledge Base:

Q81856 Excel: Functions Recalculated If New Data or Cells Entered

REFERENCES

For more information about ActiveX controls, click the Index tab in Microsoft Excel 97 Help, type the following text

ActiveX

and then double-click the selected text to go to the "Add buttons, check boxes, or other controls to a worksheet" topic.

Additional query words: 97 XL97 drop down combo box list text check option button

Keywords : kbprg
Version : WINDOWS:
Platform : WINDOWS
Issue type : kbprb