Microsoft Office Tutorials and References
In Depth Information
Examining Worksheet Events
Examining Worksheet Events
The events for a Worksheet object are some of the most useful, because most of what happens
in Excel occurs on a worksheet. Monitoring these events can make your applications perform
feats that would otherwise be impossible.
Table 19-2 lists the most commonly used worksheet events, with a brief description of each.
Table 19-2: Commonly Used Worksheet Events
Event
Action That Triggers the Event
Activate
The worksheet is activated.
BeforeDoubleClick
The worksheet is double-clicked.
BeforeRightClick
The worksheet is right-clicked.
Calculate
The worksheet is calculated (or recalculated).
Change
Cells on the worksheet are changed by the user or by an external link.
Deactivate
The worksheet is deactivated.
FollowHyperlink
A hyperlink on the sheet is clicked.
PivotTableUpdate
A pivot table on the sheet is updated.
SelectionChange
The selection on the worksheet is changed or refreshed.
Remember that the code for a worksheet event must be stored in the code module for the
specific worksheet.
To quickly activate the code module for a worksheet, right-click the sheet tab and then
choose View Code.
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
 
Search JabSto ::




Custom Search