Worksheet Level

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

EventDescription36516131007
Activate
Cast (DocEvents_Event)
(Worksheet and Chart sheet) Occurs when this worksheet is selected. This should be used instead of the OnSheetActivate property.YYYYY
BeforeDeleteOccurs before the worksheet is deleted.YYY  
BeforeDoubleClickOccurs before any cell on this worksheet is double clicked. This should be used instead of the OnDoubleClick property.YYYYY
BeforeRightClickOccurs before any cell is right mouse clicked.YYYYY
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.YYYYY
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.
YYYYY
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.YYYYY
FollowHyperLink(Worksheet only) Occurs when you click any hyperlink on a worksheet.YYYYY
LensGalleryRenderCompleteOccurs when a callout gallery's icons have completely rendered (dynamic and static)YYY  
MouseDown(Chart sheet only) Occurs when a mouse button is pressed while the pointer is over an embedded chart or chart sheetYYYYY
MouseMove(Chart sheet only) Occurs when the mouse pointer is moved over the chartYYYYY
MouseUp(Chart sheet only) Occurs when a mouse button is released while the pointer is over an embedded chart or chart sheetYYYYY
PivotTableAfterValueChangesOccurs after a cell or range of cells inside a pivot table are edited or recalculated (when cells contain formulas).YYYY 
PivotTableBeforeAllocateChangesOccurs before changes are applied to a pivot table.YYYY 
PivotTableBeforeCommitChangesOccurs before changes are committed against the OLAP data source for a pivot table.YYYY 
PivotTableBeforeDiscardChangesOccurs before changes to a pivot table are discarded.YYYY 
PivotTableChangeSyncOccurs after changes to a pivot table.YYYY 
PivotTableUpdate(Worksheet only) Occurs after a pivot table report is updated on a worksheet.YYYYY
Resize(Chart sheet only) Occurs when a chart is resizedYYYYY
SelectEvent(Chart sheet only) Occurs when a chart element is selectedYYYYY
SelectionChange(Worksheet only) Occurs when the selection is changed on the worksheet.YYYYY
SeriesChange(Chart sheet only) Occurs when a value of a chart data point changes.YYYYY
TableUpdateOccurs after a query table connected to the Data Model is updated on a worksheet.YYY  

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


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