Excel

Application Pivot Table Events

The PivotTable events for application monitor all workbooks that hold pivot tables. If you’ve not yet created a PivotTable, see Creating a Pivot Table. Using these events, you can determine when a PivotTable report opened or closed the connection to its data source in any workbook. You can also monitor when a PivotTable was updated in any opened workbook.

To program application events you need to create an object variable that represents the Excel’s Application object and propagate its events, see How to Listen Application Events. To write event listener procedures you need to open VBE, press Alt+F11 to open the VBE and write the following code in the ThisWorkbook code module to enable events for Excel application object. You must close and reopen the workbook for these changes to take effect: 

Option Explicit
Dim WithEvents App As Application
Private Sub Workbook_Open()
 Set App = Application
End Sub

Activating application object events:

In this tutorial we’ll discuss the following events for application object:

SheetPivotTableAfterValueChange

Syntax: App_SheetPivotTableAfterValueChange(Sh, TargetPivotTable, TargetRange)

The SheetPivotTableAfterValueChange event occurs after a cell or range of cells inside a PivotTable are edited (or recalculated if the cells contain a formula).

The procedure uses three arguments:

  1. Sh
    The Sheet object the pivot table is on.
  2. TargetPivotTable
    The pivot table with the changed cells.
  3. TargetRange
    The range that was changed.

This event occurs for any sheet in any opened workbook, use following event-procedures for a particular workbook or a particular worksheet:

App_SheetPivotTableAfterValueChange Example:

Option Explicit

Public WithEvents App As Application

Private Sub Workbook_Open()
 Set App = Application
End Sub

Private Sub App_SheetPivotTableAfterValueChange(ByVal Sh As Object, ByVal TargetPivotTable As PivotTable, ByVal TargetRange As Range)
 'Your code
End Sub

SheetPivotTableBeforeAllocateChanges

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

The SheetPivotTableBeforeAllocateChanges event occurs before a pivot table is updated from its OLAP data source. The event listener procedure uses five arguments:

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

This event occurs for any sheet in any opened workbook, use following event-procedures for a particular workbook or a particular worksheet:

App_SheetPivotTableBeforeAllocateChanges Example:

Option Explicit

Public WithEvents App As Application

Private Sub Workbook_Open()
 Set App = Application
End Sub

Private Sub App_SheetPivotTableBeforeAllocateChanges(ByVal Sh As Object, ByVal TargetPivotTable As PivotTable, ByVal ValueChangeStart As Long, ByVal ValueChangeEnd As Long, Cancel As Boolean)
 'Your code
End Sub

SheetPivotTableBeforeCommitChanges

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

The SheetPivotTableBeforeCommitChanges event occcurs before an OLAP pivot table updates its data source. The event listener procedure uses five arguments:

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

This event occurs for any sheet in any opened workbook, use following event-procedures for a particular workbook or a particular worksheet:

App_SheetPivotTableBeforeCommitChanges Example:

Option Explicit

Public WithEvents App As Application

Private Sub Workbook_Open()
 Set App = Application
End Sub

Private Sub App_SheetPivotTableBeforeCommitChanges(ByVal Sh As Object, ByVal TargetPivotTable As PivotTable, ByVal ValueChangeStart As Long, ByVal ValueChangeEnd As Long, Cancel As Boolean) 
 'Your code
End Sub

SheetPivotTableBeforeDiscardChanges

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

The SheetBeforeRightClick occurs occurs before an OLAP pivot table discards changes from its data source. The event listener procedure has four arguments:

  1. Sh
    The Sheet where the pivot table is on.
  2. TargetPivotTable
    The updated pivot table.
  3. ValueChangeStart
    Index number of the first change.
  4. ValueChangeEnd
    Index number of the last change.

This event occurs for any sheet in any opened workbook, use following event-procedures for a particular workbook or a particular worksheet:

App_SheetPivotTableBeforeDiscardChanges Example:

Option Explicit

Public WithEvents App As Application

Private Sub Workbook_Open()
 Set App = Application
End Sub

Private Sub App_SheetPivotTableBeforeDiscardChanges(ByVal Sh As Object, ByVal TargetPivotTable As PivotTable, ByVal ValueChangeStart As Long, ByVal ValueChangeEnd As Long)
 'Your code
End Sub

SheetPivotTableUpdate

Syntax: App_SheetPivotTableUpdate(Sh, Target)

The SheetPivotTableUpdate event occurs when the user updates a pivot table. The event procedure has two argument:

  1. Sh
    The Sheet where the pivot table is on.
  2. Target
    The updated pivot table.

This event occurs for any sheet in any opened workbook, use following event-procedures for a particular workbook or a particular worksheet:

App_SheetPivotTableUpdateExample:

Option Explicit

Public WithEvents App As Application

Private Sub Workbook_Open()
 Set App = Application
End Sub

Private Sub App_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
 'Your code 
End Sub

WorkbookPivotTableCloseConnection

Syntax: App_WorkbookPivotTableCloseConnection(Sh, Target)

The WorkbookPivotTableCloseConnection event occurs after a PivotTable report connection has been closed in any opened workbook. The event listener procedure has two arguments:

  1. Wb
    Workbook that triggered the event.
  2. Target
    Pivot table that has closed the connection.

This event occurs for all opened workbook, use Workbook_PivotTableCloseConnection procedure for a particular workbook.

App_WorkbookPivotTableCloseConnection Example:

Option Explicit

Public WithEvents App As Application

Private Sub Workbook_Open()
 Set App = Application
End Sub

Private Sub App_WorkbookPivotTableCloseConnection()
 'Your code
End Sub

WorkbookPivotTableOpenConnection

Syntax: App_WorkbookPivotTableOpenConnection(Wb, Target)

The WorkbookPivotTableOpenConnection event occurs after a PivotTable report connection has been opened in any workbook. The event listener procedure uses two argument:

  1. Wb
    Workbook that triggered the event.
  2. Target
    Pivot table that has opened the connection.

This event occurs for all opened workbook, use Workbook_PivotTableOpenConnection procedure for a particular workbook.

App_PivotTableOpenConnection Example:

Option Explicit

Public WithEvents App As Application

Private Sub Workbook_Open()
 Set App = Application
End Sub

Private Sub App_WorkbookPivotTableOpenConnection(ByVal Wb As Workbook, ByVal Target As PivotTable)
 'Your code
End Sub

WorkbookRowsetComplete

Syntax: App_WorkbookRowsetComplete(Sh, Target)

The WorkbookRowsetComplete event occurs when the user drills through a record set or calls upon the row set action on an OLAP pivot table. This event has four arguments:

  1. Wb
    Workbook that triggered the event.
  2. Description
    Description of the event.
  3. Sheet
    Name of the sheet on which the record set is created.
  4. Success
    Boolean value, true indicates success
Option Explicit

Public WithEvents App As Application

Private Sub Workbook_Open()
 Set App = Application
End Sub

Private Sub App_WorkbookRowsetComplete(ByVal Wb As Workbook, ByVal Description As String, ByVal Sheet As String, ByVal Success As Boolean)
 'Your code
End Sub