Trapping a pivot table change (MS Office 2000)

Topic

New Reply

When I change a selection in a pivot table page item, the table’s length and/or width changes based on the underlying data.

Below the last (Grand Total) row, I want to insert two rows of formulas. The location of these formulas must also be dynamic.

The worksheet_calculate event looks like this:

Private Sub Worksheet_Calculate()
……Dim myLastCell As String

……If PivotPageItemSelectionWasChanged Then
………Application.Calculation = xlManual
………Remove_Formulas ‘remove formulas from old location (if not removed by pivot)
………Insert_Formulas ‘insert formulas at new location
……..Application.Calculation = xlCalculationAutomatic
……End If

……GotoLastCell
……myLastCell = ActiveCell.Address(ReferenceStyle:=xlA1)
……ActiveSheet.PageSetup.PrintArea = “$A$1:” & myLastCell
……Application.StatusBar = “Lastcell: ” & myLastCell
End Sub

I can’t figure out how to trap a change in the pivot so I can set the boolean PivotPageItemSelectionWasChanged var to TRUE.

It would also be preferable if I could remove the old formulas before the pivot was changed.

Anybody know how?

Reply | Quote

Replies

Hello Ken —

A simplistic approach could be to

* name the formula rows (to start with)

Then, in your Worksheet_Calculate module,
* delete the named rows — even if the pivot table has not changed its dimensions
i’m assuming that the formula rows are stored for copy-paste somewhere else,
or you can re-create them at run-time

* reset the used range — ActiveSheet.UsedRange will do that
This is the

* re-insert the formula rows after the last row, wherever it is

* rename the formula rows (for the next time round)

Hope this helps… Ciao
Khushnood

Reply | Quote

Hello Ken —

Sorry about the first (incomplete) post

A simplistic approach could be to

* name the formula rows (to start with)

Then, in your Worksheet_Calculate module,
* delete the named rows — even if the pivot table has not changed its dimensions
i’m assuming that the formula rows are stored for copy-paste somewhere else,
or you can re-create them at run-time

* reset the used range — ActiveSheet.UsedRange will do that
This is the line which allows your used range to be tracked dynamically
whenever the PivotTable changes in dimensions

* re-insert the formula rows after the last row, wherever it is

* rename the formula rows (for the next time round)

Hope this helps… Ciao
Khushnood

Reply | Quote

Hi Khushnood,

Thanks for reply. I probably wasn’t entirely clear on what I am having a problem with.

I would like to be able to trap the Pivot_Change event (if such an event exists) when the user makes a different selection from a Page field(cell).

As I have it now, the code is being executed when the Worksheet_Calculate event occurs. That means it also occurs when something other than a Pivot_Change event occurs.

I came up with a solution that works, but requires a few extra clicks. The solution consists of a message box that asks whether the pivot has changed. Only when I click the Yes button will it execute the code that removes the formulas from the old location and places them in the new location. It works.

Private Sub Worksheet_Calculate()
……Dim myLastCell As String
……Dim myResponse As Integer

……myResponse = MsgBox(“Was pivot changed?”, vbYesNo, “Information required”)
……If myResponse = vbYes Then
…………Application.Calculation = xlManual
…………Remove_Formulas ‘remove formulas from old location
…………Insert_Formulas ‘insert formulas at new location

…………GotoLastCell
…………myLastCell = ActiveCell.Address(ReferenceStyle:=xlA1)
…………ActiveSheet.PageSetup.PrintArea = “$A$1:” & myLastCell
…………Application.StatusBar = “Lastcell: ” & myLastCell
…………Application.Calculation = xlCalculationAutomatic
……End If
End Sub

RE: i’m assuming that the formula rows are stored for copy-paste somewhere else, or you can re-create them at run-time.

I just re-create them at runtime – inside a loop. Had not considered the copy/paste solution. See, I learn something different each time.

Reply | Quote

Hi Ken

I couldn’t find a ‘PivotTable_Change’ property in the VBA Object Browser (you can press F2 in the Visual Basic Editor window to get the Object Browser)

Nor could I find any PivotTable property in the VBA online help which might help.

Maybe PivotTables in Excel XP has such a property ? (hopefully !)

You’re absolutely right about learning something new each time !!!
I experience that ALL the time…

Glad to have been of help… Ciao
Khushnood

Reply | Quote

Excel 2002 has a worksheet event based on a Pivot Table update.

Andrew C

Reply | Quote