Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Winnipeg, Canada
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Trapping a pivot table change (MS Office 2000)

    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?

  2. #2
    Lounger
    Join Date
    Jan 2002
    Location
    Mumbai, India
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trapping a pivot table change (MS Office 2000)

    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

  3. #3
    Lounger
    Join Date
    Jan 2002
    Location
    Mumbai, India
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trapping a pivot table change (MS Office 2000)

    Hello Ken --

    Sorry about the first (incomplete) post <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

    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

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Winnipeg, Canada
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trapping a pivot table change (MS Office 2000)

    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. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  5. #5
    Lounger
    Join Date
    Jan 2002
    Location
    Mumbai, India
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trapping a pivot table change (MS Office 2000)

    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

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Trapping a pivot table change (MS Office 2000)

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

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

    Andrew C

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •