Excel

PivotTable Events for Worksheets

In this tutorial, you’ll learn how to use events with a Pivot table. With a pivot table, you can transform millions-row of raw data into a summary report in seconds, without having to write new formulas. In addition to quickly summarizing and calculating data, pivot tables enable you to change your analysis on the fly by simply moving fields from one area of a report to another.

Creating a Basic Pivot Table

Create some sample data, as shown in the following image:
Raw data for pivot table

Select the range of cells (including row and column titles) or select a single cell in the range and Excel will expand the range automatically. Click Insert > Tables > PivotTable.
Insert tab to create PivotTable

The Create PivotTable dialog box opens. Excel automatically chooses “Select a table or range”, with the cell range that you selected.

Select “New Worksheet” to create a new worksheet for the pivot table or choose “Existing Worksheet” to insert the pivot table on a worksheet which already in your workbook. Click OK.

Excel inserts the new pivot table PivotTable1, you can change the table name. The pivot table appears as an empty placeholder until you define the rows, columns, and values to use to summarize the source data.

When you select a cell inside the pivot table, Excel displays the PivotTable Fields pane on the right, which lists all the columns in the source data.

Select all fields from the PivotTable Field List:

You’ll see the following pivot table after selecting all the fields:

PivotTable Events

The worksheet event-handler procedures must be in the code module for that worksheet. Put them somewhere else, and they won’t work. You can quickly access that code window by right-clicking the worksheet’s tab and selecting the View Code:

View Code

  1. PivotTableAfterValueChange
  2. PivotTableBeforeAllocateChanges
  3. PivotTableBeforeCommitChanges
  4. PivotTableBeforeDiscardChanges
  5. PivotTableChangeSync
  6. PivotTableUpdate

Worksheet_PivotTableAfterValueChange

This event fires after the user edit cells inside a pivot table or recalculate them if they contain a formula.

Private Sub Worksheet_PivotTableAfterValueChange(ByVal TargetPivotTable As PivotTable, ByVal TargetRange As Range)
 MsgBox "Value changed"
End Sub

TargetPivotTable is the pivot table with the changed cells. TargetRange is the range that was changed.

Worksheet_PivotTableBeforeAllocateChanges

This event fires before a pivot table is updated from its OLAP data source (before executing the UPDATE CUBE statement).

Private Sub Worksheet_PivotTableBeforeAllocateChanges(ByVal TargetPivotTable As PivotTable, ByVal ValueChangeStart As Long, ByVal ValueChangeEnd As Long, Cancel As Boolean)
 MsgBox "Before allocate change"
End Sub

TargetPivotTable is the updated pivot table, ValueChangeStart is the index number of the first change, ValueChangeEnd is the index number of the last change, setting Cancel to True prevents the changes from being applied to the pivot table.

Worksheet_PivotTableBeforeCommitChanges

This event occurs when a user has chosen to apply changes to an OLAP pivot table’s data source.

Private Sub Worksheet_PivotTableBeforeCommitChanges(ByVal TargetPivotTable As PivotTable, ByVal ValueChangeStart As Long, ByVal ValueChangeEnd As Long, Cancel As Boolean)
 MsgBox "Before commit change"
End Sub

TargetPivotTable is the updated pivot table, ValueChangeStart is the index number of the first change, ValueChangeEnd is the index number of the last change, setting Cancel to True prevents changes from being applied to the data source.

Worksheet_PivotTableBeforeDiscardChanges

This event fires when a user has chosen to roll back the changes made to an OLAP pivot table’s data source.

Private Sub Worksheet_PivotTableBeforeDiscardChanges(ByVal TargetPivotTable As PivotTable, ByVal ValueChangeStart As Long, ByVal ValueChangeEnd As Long)
 MsgBox "Before discard change"
End Sub

TargetPivotTable is the pivot table with changes to discard, ValueChangeStart is the index number of the first change, ValueChangeEnd is the index number of the last change.

Worksheet_PivotTableChangeSync

This event fires after a pivot table has been changed or refreshed. For example, when a user clear, group or refresh items in the table.

Private Sub Worksheet_PivotTableChangeSync(ByVal Target As PivotTable)
 MsgBox "Change syn"
End Sub

Target is the pivot table that has been changed.

Worksheet_PivotTableUpdate

The Worksheet_PivotTableUpdate event fires after a pivot table is updated or refreshed on a worksheet.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
 MsgBox "update"
End Sub

Target is the pivot table that has been updated or refreshed.