The Change event

The Change event is triggered when any cell in a worksheet is changed by the user or by a VBA procedure. The Change event is not triggered when a calculation generates a different value for a formula or when an object is added to the sheet.

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 procedure is executed whenever the worksheet is changed. It displays a message box that shows the address of the Target range:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

MsgBox "Range " & Target.Address & " was changed." End Sub

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 that 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 should not trigger the event do!

♦ Changing the formatting of a cell does not trigger the Change event (as expected), but choosing the Edit ^ Clear^ Formats command does trigger the event.

♦ Adding, editing, or deleting a cell comment does not trigger the Change event.

♦ Pressing Delete generates an event even if the cell is empty to start with.

♦ Cells that are changed by using Excel commands may or may not trigger the Change event. For example, the Data^Form command and the Data ^ Sort command do not trigger the event. But the Tools ^ Spelling command and the Edit^Replace command do trigger the Change event.

♦ 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.

To add to the confusion,triggers for the Change event vary,depending on the version of Excel. For versions earlier than Excel 2002, filling a range by choosing the Edit^ Fill command does not generate a Change event — nor does choosing the Edit^ Delete command to delete cells.

+1 0

Post a comment