Edit

Worksheet object events

  • Article

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

See also

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.