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 one of the following events from the Procedure list box.
Worksheet-level events occur when a worksheet is activated, when the user changes a worksheet cell, or when the PivotTable 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
Support and feedback
Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.