Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Worksheet Object Events

Worksheet Object Events

Office 2007

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 drop-down 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



Community Additions

ADD
Show:
© 2015 Microsoft