Knowledge Base

XL: Change Event for ActiveX Control Runs Unexpectedly

Article ID: 169885

Article Last Modified on 1/19/2007

Microsoft Excel 2000 Standard Edition
Microsoft Excel 2002 Standard Edition
Microsoft Excel 97 Standard Edition

This article was previously published under Q169885

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 that contains a volatile function.

-or-
A cell that contains a formula that contains 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).

MORE INFORMATION

In Microsoft Excel, you can insert controls, for example a list box, drop-down box, and edit box, into a worksheet. You can use the ListFillRange property of the control 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()

REFERENCES

For more information about ActiveX controls, click Microsoft Excel Help on the Help menu, type ActiveX in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

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

Keywords: kbprb kbprogramming KB169885