Microsoft Office Tutorials and References
In Depth Information
Running a Macro When a Cell Changes
The code in the Standard module is used to display the UserForm. This
procedure has been assigned to a button in the example file for easy use.
The UserForm includes one text box, which is the control that is attached to the
code. To use this in another UserForm, copy the code from the KeyPress event
to the respective events of other text box(es).
The macro in the sample file runs automatically.
Exl
Running a Macro When a Cell Changes
This procedure demonstrates how to run a macro when the value of one or more
cells changes.
Example file:
E014.xls
Scenario: Sometimes, you may want to run a macro
whenever the value of a cell changes, for instance to bring
up a dialog telling a user that the amount they’ve entered is
over $10,000 and requires special consideration.
A common but false approach to accomplish this is to create a formula, such as:
=IF(A1=10, "Macro1", "")
This does not work, of course. Macros cannot be run by writing a formula into a
cell. This sample shows how to run a macro when the value of a cell changes,
using the condition that if A1 equals 10, B1 does not equal 5 and C1 is greater
than 10, as expressed on the following page:
A1 = 10, B1 <> 5 and C1 > 10
This procedure uses either the Calculate event or the Change event. The
problem with the Calculate event is that it is triggered on each calculation, and
could also be run several times, after A1 equals 10, because the condition is
always true.
The use of the Change event requires the use of dependents (dependent cells),
because when the formula is evaluated, A1 is the changing cell, not the cell
with the formula.
 
Search JabSto ::




Custom Search