Part VI: Automating Excel
To get a feel for the types of actions that generate the Change event for a worksheet, enter
the preceding procedure into the code module for a Worksheet object. After you enter this
procedure, activate Excel and, using various techniques, make changes to the worksheet.
Every time the Change event occurs, a message box displays the address of the range that
changed.Unfortunately, the Change event doesn’t always work as expected. For example,■ (^) Changing the formatting of a cell does not trigger the Change event (as expected),
but choosing Home ➪ Editing ➪ Clear ➪ Clear Formats does.
■ (^) Pressing Delete generates an event even if the cell is empty at the start.
■ Cells changed via Excel commands may or may not trigger the Change event. For
example, sorting and goal-seeking operations do not trigger the Change event.
However, operations such as Find and Replace, using the AutoSum button, or add-
ing a Total row to a table do trigger the event.
■ If your VBA procedure changes a cell, it does trigger the Change event.
Monitoring a specific range for changes
Although the Change event occurs when any cell on the worksheet changes, most of the
time you’ll be concerned only with changes that are 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 corresponds to the cell(s) that changed.
Assume that your worksheet has a range named InputRange, and you want your VBA code
to monitor changes to this range only. No Change event exists for a Range object, but you
can perform a quick check within the Worksheet_Change procedure. The following proce-
dure demonstrates this:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim VRange As Range
Set VRange = Me.Range("InputRange")
If Union(Target, VRange).Address = VRange.Address Then
Msgbox "The changed cell is in the input range."
End if
End Sub
This example creates a Range object variable named VRange, which represents the work-
sheet range that you want to monitor for changes. The procedure uses the VBA Union func-
tion to determine whether VRange contains the Target range (passed to the procedure in
its argument). The Union function returns an object that consists of all the cells in both
its arguments. If the range address is the same as the VRange address, VRange contains
Target, and a message box appears. Otherwise, the procedure ends, and nothing happens.
The preceding procedure has a potential flaw: Target may consist of a single cell or a
range. For example, if the user changes more than one cell at a time, Target becomes a
multicell range. As it is, all of the changed cells must be within InputRange. If you still
want to act on cells within InputRange, even if some of the changes are not, you have to