Worksheet Object Events
Events on sheets are enabled by default. To view the event procedures for a sheet, right-click the sheet tab and click View Code on the shortcut menu. Select the event name from the Procedure drop-down list box.
Activate | Deactivate |
Worksheet-level events occur when a worksheet is activated, the user changes a worksheet cell, or when PivotTable is changes. The following example adjusts the size of columns A through F whenever the worksheet is recalculated.
Private Sub Worksheet_Calculate()
Columns("A:F").AutoFit
End Sub
Some events can be used to substitute an action for the default application behavior, or to make a small change to the default behavior. The following example traps the right-click event and adds a new menu item to the shortcut menu for cells B1:B10.
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _
Cancel As Boolean)
For Each icbc In Application.CommandBars("cell").Controls
If icbc.Tag = "brccm" Then icbc.Delete
Next icbc
If Not Application.Intersect(Target, Range("b1:b10")) _
Is Nothing Then
With Application.CommandBars("cell").Controls _
.Add(Type:=msoControlButton, before:=6, _
temporary:=True)
.Caption = "New Context Menu Item"
.OnAction = "MyMacro"
.Tag = "brccm"
End With
End If
End Sub