Page 301 - Microsoft Office Excel 2003 Programming Inside Out

Understanding and Using Events

Change Event
The Change event occurs when any cell in a worksheet is changed by the user, by VBA code,
or by an external link. The Change event is not triggered when a calculation generates a dif­
ferent value for a formula, or when an object is added to the worksheet.

Note The Change event does not occur when cells change during recalculation. Use the
Calculate event to trap a sheet recalculation.

When the Worksheet_Change procedure is executed, it receives a Range object as its Target
argument. This Range object represents the changed cell or range that triggered the event.
The following event procedure displays the address of the Target range:

Private Sub Worksheet_Change (ByVal Target As Excel.Range)
MsgBox "Range " & Target.Address & " was changed."
End Sub

Inside Out

The Quirky Change Event
To get a better grasp of what causes the Change event to trigger, type the previous proce­
dure into a code module and start modifying your worksheet. Every time the Change event
occurs, you will see the address of the range that was modified.
There are some quirks associated with the Change event that you should be aware of, such
as actions that should trigger the Change event but don’t, as well as actions that do trigger
the Change event when they should not. The following list highlights some of these quirks:

● Changing the format of the cell does not trigger the Change event, but using the Clear
Formats command from the Edit menu does trigger the event.
● Inserting, editing, or deleting a cell comment does not trigger the Change event.
● Pressing the Delete or Backspace key and then pressing the Enter key triggers the
Change event, even if the cell is empty.
● Cells that are changed by using Excel commands might or might not trigger a change
event. For example, adding new records to a Data Form or sorting data does not trig­
ger the Change event. However, if you have made any spelling changes in your work-
sheet, using the Excel Spell checking feature or using the Replace feature will
generate a Change event.
As you can see by the inconsistencies with the preceding list, it isn’t a good idea to rely on
the Change event to detect all cell changes. However, you can work around these problems
if you are aware of them. For example, if you know that the cell contents are required to
have a specific format or value, you can use the BeforeSave event procedure to verify that Chapter 12
the Change event did not miss an invalid entry.

275
Part 4: Advanced VBA