Worksheet Events

The following worksheet event procedures are available in the code module behind each worksheet:

□ Private Sub Worksheet_Activate()

□ Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

□ Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As

Boolean)

Private Sub Worksheet

_Calculate()

Private Sub Worksheet

_Change(ByVal Target As Range)

Private Sub Worksheet

_Deactivate()

Private Sub Worksheet

_FollowHyperlink(ByVal Target As Hyperlink)

Private Sub Worksheet

_PivotTableUpdate(ByVal Target As PivotTable)

Private Sub Worksheet

_SelectionChange(ByVal Target As Range)

You should use the drop-down menus at the top of the code module to create the first and last lines of any procedure you want to use. For example, in a worksheet code module, you can select the Worksheet object from the left-hand drop-down list. This will generate the following lines of code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

The SelectionChange event is the default event for the Worksheet object. If you want a different event, select the event from the right-hand drop-down list, and delete the preceding lines of code.

As an alternative to using the drop-downs, you can type the first line of the procedure yourself, but by doing so, it's easy to make mistakes. The arguments must correspond in number, order, and type with the arguments specified for each event procedure. You are permitted to use different parameter names if you wish, but it is better to stick with the standard names to avoid confusion.

Most parameters must be declared with the ByVal keyword, which prevents your code from passing back changes to the object or item referenced by assigning a new value to the parameter. If the parameter represents an object, you can change the object's properties and execute its methods, but you cannot pass back a change in the object definition by assigning a new object definition to the parameter.

Some event procedures are executed before the associated event occurs and have a Cancel parameter that is passed by reference. You can assign a value of True to the Cancel parameter to cancel the associated event. For example, you could prevent a user accessing the worksheet shortcut menu by canceling the RightClick event in the Worksheet_BeforeRightClick event procedure:

Private Sub Worksheet_BeforeRightClick(ByVal

Target

As Range, _

Cancel

As Boolean)

Cancel = True

End Sub

0 0

Post a comment