Excel 2010 Bible

Chapter 43: Working with Excel Events

881

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 argu-

ment. This Range object corresponds to the cell or cells that changed.

Assume that your worksheet has a range named InputRange, and you want 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 procedure demonstrates this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim VRange As Range

Set VRange = 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 worksheet

range that you want to monitor for changes. The procedure uses the VBA Union function to deter-

mine 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 of 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 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. Therefore, the

procedure requires modification to loop through all the cells in Target. The following procedure

checks each changed cell and displays a message box if the cell is within the desired range:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Set VRange = Range(“InputRange”)

For Each cell In Target

If Union(cell, VRange).Address = VRange.Address Then

Msgbox “The changed cell is in the input range.”

End if

Next cell

End Sub

On the CD
A workbook with this example is available On the CD-ROM the file is named monitor a range.xlsm.