Worksheet Level

There are 23 different events.
More details on how to add these events can be found here: VBA > Events > Worksheet Level

EventDescription3651916131007
Activate
Cast (DocEvents_Event)
(Worksheet and Chart sheet) Occurs when this worksheet is selected. This should be used instead of the OnSheetActivate property.YYYYYY
BeforeDeleteOccurs before the worksheet is deleted.YYYY  
BeforeDoubleClickOccurs before any cell on this worksheet is double clicked. This should be used instead of the OnDoubleClick property.YYYYYY
BeforeRightClickOccurs before any cell is right mouse clicked.YYYYYY
Calculate
Cast (DocEvents_Event)
Occurs before any cell on this worksheet is re-calculated. This event will also capture table filter changes as long as the table has a Total Row enabled. This should be used instead of the OnCalculate property.YYYYYY
Change(Worksheet and Chart sheet) Occurs when any cell on this worksheet is changed either by the user or by an external link. This event is not fired if the value changes as the result of a calculation. This should be used instead of the OnEntry property.
Passes a Range as a parameter for the range of cells that was changed.
YYYYYY
Deactivate(Worksheet and Chart sheet) Occurs when the user moves focus to another worksheet. This event does not fire when the user moves between two windows displaying the same worksheet.YYYYYY
FollowHyperLink(Worksheet only) Occurs when you click any hyperlink on a worksheet.YYYYYY
LensGalleryRenderCompleteOccurs when a callout gallery's icons have completely rendered (dynamic and static)YYYY  
MouseDown(Chart sheet only) Occurs when a mouse button is pressed while the pointer is over an embedded chart or chart sheetYYYYYY
MouseMove(Chart sheet only) Occurs when the mouse pointer is moved over the chartYYYYYY
MouseUp(Chart sheet only) Occurs when a mouse button is released while the pointer is over an embedded chart or chart sheetYYYYYY
PivotTableAfterValueChangesOccurs after a cell or range of cells inside a pivot table are edited or recalculated (when cells contain formulas).YYYYY 
PivotTableBeforeAllocateChangesOccurs before changes are applied to a pivot table.YYYYY 
PivotTableBeforeCommitChangesOccurs before changes are committed against the OLAP data source for a pivot table.YYYYY 
PivotTableBeforeDiscardChangesOccurs before changes to a pivot table are discarded.YYYYY 
PivotTableChangeSyncOccurs after changes to a pivot table.YYYYY 
PivotTableUpdate(Worksheet only) Occurs after a pivot table report is updated on a worksheet.YYYYYY
Resize(Chart sheet only) Occurs when a chart is resizedYYYYYY
SelectEvent(Chart sheet only) Occurs when a chart element is selectedYYYYYY
SelectionChange(Worksheet only) Occurs when the selection is changed on the worksheet.YYYYYY
SeriesChange(Chart sheet only) Occurs when a value of a chart data point changes.YYYYYY
TableUpdateOccurs after a query table connected to the Data Model is updated on a worksheet.YYYY  

VBA Sample

Private Sub Worksheet_Activate() 
End Sub

Private Sub Worksheet_BeforeDelete()
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
                                        ByRef Cancel As Boolean)
End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _
                                       ByRef Cancel As Boolean)
End Sub

Private Sub Worksheet_Calculate()
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
End Sub

Private Sub Worksheet_Deactivate()
End Sub

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
End Sub

Private Sub Worksheet_LensGalleryRenderComplete()
End Sub

Private Sub Worksheet_PivotTableAfterValueChange( _
          ByVal TargetPivotTable As PivotTable, _
          ByVal TargetRange As Range)
End Sub

Private Sub Worksheet_PivotTableBeforeAllocateChanges( _
          ByVal TargetPivotTable As PivotTable, _
          ByVal ValueChangeStart As Long, _
          ByVal ValueChangeEnd As Long, _
          ByRef Cancel As Boolean)
End Sub

Private Sub Worksheet_PivotTableBeforeCommitChanges( _
          ByVal TargetPivotTable As PivotTable, _
          ByVal ValueChangeStart As Long, _
          ByVal ValueChangeEnd As Long, _
          ByRef Cancel As Boolean)
End Sub

Private Sub Worksheet_PivotTableBeforeDiscardChanges( _
          ByVal TargetPivotTable As PivotTable, _
          ByVal ValueChangeStart As Long, _
          ByVal ValueChangeEnd As Long)
End Sub

Private Sub Worksheet_PivotTableChangeSync(ByVal Target As PivotTable)

End Sub

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub

Private Sub Worksheet_TableUpdate(ByVal Target As TableObject)
End Sub

Example

This example shows you how to add an item to the cell shortcut menu for all the cells in the range "A1:G200".

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, ByRef Cancel As Boolean) 
   For Each ctlcontrol In Application.CommandBars("cell").Controls
      If ctlcontrol.Tag = "added" Then ctlcontrol.Delete
   Next ctlcontrol
   If Not Application.Intersect(Target, Range("A1:G200")) Is Nothing Then
      With Application.CommandBars("cell").Controls.Add( _
               Type:=msoCommandButton, before:=6, temporary:=True)
         .OnAction = "ExtraCommand"
         .Tag = "added"
      End With
   End If
End Sub

Public Sub ExtraCommand
'do something
End Sub


© 2020 Better Solutions Limited. All Rights Reserved. © 2020 Better Solutions Limited TopPrevNext