Excel 2010 Bible

Part VI: Programming Excel with VBA

880

TABLE 43.2 (continued)

Event Action That Triggers the Event

Calculate The worksheet is calculated (or recalculated).

Change Cells on the worksheet are changed by the user.

Deactivate The worksheet is deactivated.

FollowHyperlink A hyperlink on the worksheet is clicked.

PivotTableUpdate A PivotTable on the worksheet has been updated.

SelectionChange The selection on the worksheet is changed.

Using the Change event

A Change event is triggered when any cell in the worksheet is changed by the user. A Change

event is not triggered when a calculation generates a different value for a formula or when an

object (such as a chart or SmartArt) is added to the sheet.

When the Worksheet_Change procedure executes, it receives a Range object as its Target

argument. This Range object corresponds to the changed cell or range that triggered the event.

The following example 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 feel for the types of actions that generate the Change event for a worksheet, enter the pre-

ceding procedure into the code module for a Worksheet object. After entering 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

l (^) Changing the formatting of a cell does not trigger the Change event (as expected), but
choosing Home ➪ Editing ➪ Clear ➪ Clear Formats does.
l (^) Pressing Delete generates an event even if the cell is empty at the start.
l Cells changed via Excel commands may or may not trigger the Change event. For exam-
ple, sorting and goal seeking operations do not trigger the Change event. However, oper-
ations such as Find and Replace, using the AutoSum button, or adding a Totals row to a
table do trigger the event.
l If your VBA procedure changes a cell, it does trigger the Change event.