Microsoft Office Tutorials and References
In Depth Information
Examining Worksheet Events
To get a better feel for the types of actions that generate a Change event for a worksheet, enter
the preceding procedure in the code module for a Worksheet object. After entering this
procedure, activate Excel and make some changes to the worksheet by using various techniques.
Every time the Change event occurs, you’ll see a message box that displays the address of the
range that was changed.
When I ran this procedure, I discovered some interesting quirks. Some actions that should trigger
the event don’t, and other actions that shouldn’t trigger the event do!
h Changing the formatting of a cell doesn’t trigger the Change event (as expected). But
copying and pasting formatting does trigger the Change event. Choosing the
Home➜Editing➜Clear➜Clear Formats command also triggers the event.
h Merging cells doesn’t trigger the Change event, even if the contents of some of the
merged cells are deleted in the process.
h Adding, editing, or deleting a cell comment doesn’t trigger the Change event.
h Pressing Delete generates an event even if the cell is empty to start with.
h Cells that are changed by using Excel commands may or may not trigger the Change
event. For example, sorting a range doesn’t trigger the event. But using the spell checker
h If your VBA procedure changes a cell, it does trigger the Change event.
As you can see from the preceding list, it’s not a good idea to rely on the Change event to detect
cell changes for critical applications.
Monitoring a specific range for changes
The Change event occurs when any cell on the worksheet is changed. But, in most cases, all you
care about are changes made to a specific cell or range. When the Worksheet_Change event
handler procedure is called, it receives a Range object as its argument. This Range object
represents the cell or cells that were changed.
Assume that your worksheet has a range named InputRange , and you’d like to monitor
changes made only within this range. There is no Change event for a Range object, but you can
perform a quick check within the Worksheet_Change procedure:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim MRange As Range
Set MRange = Range(“InputRange”)
If Not Intersect(Target, MRange) Is Nothing Then _
MsgBox “A changed cell is in the input range.”