Excel

Workbook PivotTable Events

The Workbook PivotTable events monitor all worksheets in the workbook that hold pivot tables. Using these events, you can determine when a PivotTable report opened or closed the connection to its data source and determine when the PivotTable was updated. You can also use these events to cancel the changes and to monitor if the changes has been synced or discarded.

To write event procedures, open a workbook and press Alt + F11 to open Visual Basic Editor (VBE). Then double-click the ThisWorkbook object in the Project Explorer tree to show its code module:
Workbook PivotTable Events

  1. PivotTableCloseConnection
  2. PivotTableOpenConnection
  3. SheetPivotTableAfterValueChange
  4. SheetPivotTableBeforeAllocateChanges
  5. SheetPivotTableBeforeCommitChanges
  6. SheetPivotTableBeforeDiscardChanges
  7. SheetPivotTableChangeSync
  8. SheetPivotTableUpdate

PivotTableCloseConnection

Syntax: Workbook_PivotTableCloseConnection(Target)

The PivotTableCloseConnection event occurs when a pivot table report closes its connection to its data source. To listen this event for a workbook the Workbook_PivotTableCloseConnection procedure is used. The Target argument represents the pivot table that has closed the connection.

Workbook_PivotTableCloseConnection Example:

Private Sub Workbook_PivotTableCloseConnection(ByVal Target As PivotTable)
 MsgBox "Connection closed for " & Target.Name
End Sub

PivotTableOpenConnection

Syntax: Workbook_PivotTableOpenConnection(Target)

The PivotTableOpenConnection event occurs when a pivot table report opens a connection to its data source. To listen this event for a workbook, the Workbook_PivotTableOpenConnection procedure is used. The Target argument represents the pivot table that has opened the connection.

Workbook_PivotTableOpenConnection Example:

Private Sub Workbook_PivotTableOpenConnection(ByVal Target As PivotTable)
 MsgBox "Connection opened for " & Target.Name
End Sub

SheetPivotTableAfterValueChange

Syntax: Workbook_SheetPivotTableAfterValueChange(Sh, TargetPivotTable, TargetRange)

The SheetPivotTableAfterValueChange event occurs after the user edits cells inside a pivot table or the user recalculates them if they contain a formula. To listen this event, the Workbook_SheetPivotTableAfterValueChange is used which has following arguments:

  1. Sh :
    The sheet contains pivot table.
  2. TargetPivotTable:
    The pivot table with the changed cells.
  3. TargetRange:
    Range that was changed.

Workbook_SheetPivotTableAfterValueChange Example:

Private Sub Workbook_SheetPivotTableAfterValueChange(ByVal Sh As Object, ByVal TargetPivotTable As PivotTable, ByVal TargetRange As Range)
 Sh.Cells.Interior.ColorIndex = 0
 TargetRange.Interior.ColorIndex = 3
End Sub

SheetPivotTableBeforeAllocateChanges

Syntax: Workbook_SheetPivotTableBeforeAllocateChanges(Sh, TargetPivotTable, ValueChangeStart, ValueChangeEnd, Cancel)

The SheetPivotTableBeforeAllocateChanges event occurs before a pivot table is updated from its OLAP data source. To listen this event, the Workbook_SheetPivotTableBeforeAllocateChanges procedure is used which has following arguments:

  1. Sh
    Sheet, the pivot table is on
  2. TargetPivotTable
    The updated pivot table
  3. ValueChangeStart
    The index number of the first change
  4. ValueChangeEnd
    The index number of the last change
  5. Cancel
    Setting Cancel to True prevents the changes from being applied to the pivot table.

Workbook_SheetPivotTableBeforeAllocateChanges Example:

Private Sub Workbook_SheetPivotTableBeforeAllocateChanges(ByVal Sh As Object, ByVal TargetPivotTable As PivotTable, ByVal ValueChangeStart As Long, ByVal ValueChangeEnd As Long, Cancel As Boolean)
 Cancel = True
 MsgBox "VBA cancelled the operation"
End Sub

SheetPivotTableBeforeCommitChanges

Syntax: Workbook_SheetPivotTableBeforeCommitChanges(Sh, TargetPivotTable, ValueChangeStart, ValueChangeEnd, Cancel)

The SheetPivotTableBeforeCommitChanges event occurs when a user has chosen to apply changes to an OLAP pivot table’s data source. To listen this event, the Workbook_SheetPivotTableBeforeCommitChanges procedure is used which has following arguments:

  1. Sh
    Sheet holds the pivot table.
  2. TargetPivotTable
    The updated pivot table.
  3. ValueChangeStart
    The index number of the first change.
  4. ValueChangeEnd
    The index number of the last change.
  5. Cancel
    Setting Cancel to True prevents the changes from being applied to the data source.

Workbook_SheetPivotTableBeforeCommitChanges Example:

Private Sub Workbook_SheetPivotTableBeforeCommitChanges(ByVal Sh As Object, ByVal TargetPivotTable As PivotTable, ByVal ValueChangeStart As Long, ByVal ValueChangeEnd As Long, Cancel As Boolean)
 Cancel = True
 MsgBox "VBA cancelled the operation"
End Sub

SheetPivotTableBeforeDiscardChanges

Syntax: Workbook_SheetPivotTableBeforeDiscardChanges(Sh, TargetPivotTable, ValueChangeStart, ValueChangeEnd )

The SheetPivotTableBeforeDiscardChanges event occurs when a user has chosen to roll back the changes made to an OLAP pivot table’s data source. To listen this event, the Workbook_SheetPivotTableBeforeDiscardChanges procedure is used which has following arguments:

  1. Sh
    The sheet holds the pivot table
  2. TargetPivotTable
    The pivot table with changes to discard
  3. ValueChangeStart
    Index number of the first change
  4. ValueChangeEnd
    Index number of the last change

Workbook_SheetPivotTableBeforeDiscardChanges Example:

Private Sub Workbook_SheetPivotTableBeforeDiscardChanges(ByVal Sh As Object, ByVal TargetPivotTable As PivotTable, ByVal ValueChangeStart As Long, ByVal ValueChangeEnd As Long)
 MsgBox Target.Name & " changes discarded on sheet: " & Sh.Name
End Sub

SheetPivotTableChangeSync

Syntax: Workbook_SheetPivotTableChangeSync(Sh, Target)

The SheetPivotTableChangeSync occurs after the user changes a pivot table. To listent this event, the Workbook_SheetPivotTableChangeSync procedure is used. The Sh argument represents the sheet the pivot table is on. The Target represents the pivot table that has been changed.

Workbook_SheetPivotTableChangeSync Example:

Private Sub Workbook_SheetPivotTableChangeSync(ByVal Sh As Object, ByVal Target As PivotTable)
 MsgBox Target.Name & " synced on sheet: " & Sh.Name
End Sub

SheetPivotTableUpdate

Syntax: Workbook_SheetPivotTableUpdate(Sh, Target)

The SheetPivotTableUpdate event occurs after a pivot table is updated or refreshed on any worksheet in the workbook. To listen this event, the Workbook_SheetPivotTableUpdate procedure is used. The Sh argument represents the sheet that hold the pivot table. And the Target argument represents the pivot table that has updated.

Workbook_SheetPivotTableUpdate Example:

In the following event code, when a pivot table is updated, the name of its worksheet appears in a message box.

Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
 MsgBox "The PivotTable on sheet " & Sh.Name & " has updated."
End Sub