What caused that PivotTableUpdate? Part Two.

In Part One, we considered the problem of determining what actually triggered a PivotTableUpdate (or it’s equally gormless twin, the PivotTableChangeSync event), with a view to identifying when a specific PivotField is filtered. Today we’re going to take a look at how we can find out more about what triggered those events. And what better place to glean information about the last action the user performed than here:

Undo

That’s right, the Undies stack. (That’s what we call it down-under). Or rather, the Undo stack to you uptight northerners. (Ok, enough of the innuendo and Double entendre.)

Go on, then…show us what’s in your undies…er…undo stack, Jeff:

Undo Pivot Actions 5

Wow: all of the above relate to some kind of action on a PivotTable. If only we could access that list, we’d have a pretty rich source from which to answer the title of this post. And we can indeed do just that.

If you want everything in that list, then you can use this code from MVP Siddharth Rout at msdn:

For i = 1 To xlApp.CommandBars("Standard").Controls("&Undo").Control.ListCount
UnDoList(i) = xlApp.CommandBars("Standard").Controls("&Undo").Control.List(i)
Next

…and if you just want the last undo item, you can use this:

Application.CommandBars("Standard").Controls("&Undo").List(1)

Let’s take a look at how all the different things that raise a PivotTableUpdate event get reflected in that Undo list. If an action isn’t listed in the below table, then as far as I know from my rather in-exhaustive testing, it doesn’t raise a PivotTableUpdate event.

Actions and associated undo stack text v2

There’s a few things worth noting about the above.

  • Firstly, we can now clearly determine whether the update was caused by filtering, a refresh, a structure change (in which case the Undo Stack just says ‘Pivot’, or other less common tweaks.
  • Secondly, while we can use this to confirm whether or not a PivotTableUpdate event was in fact caused by someone adjusting a PivotFilter, we still can’t tell which filter.
  • Thirdly, it’s surprising just how many things trigger an update – which is why it will be good to call out filter changes explicitly in the event that we want to sync lots of large pivots.
  • And finally, one of the actions – adding/amending/deleting a Calculated Field – actually clears the Undo Stack. How weird is that?

Okay, that’s enough for today…I’ve got to go cook my dinner. (My wife is in Spain with the kids, and so apparently it won’t cook itself). Tune in next time, when we’ll look at how we can write a routine that leverages off the undo stack, and that also helps us determine not only that a PivotField was filtered, but which PivotField it was.

4 Comments

  1. […] Part Two we worked out that we could tell via the contents of the Undo Stack whether the update was caused […]

  2. Ryan says:

    Hi Jeff, Interesting stuff. I have used similar code provided by Jerry on another forum to limit my slicer to only single selection (I wish this was a simple selection on the slicer properties although the code works great). What I am finding when my workbook cycles through refresh pivots macro (to refresh all pivots with the latest data in my workbook), it throws an error (Method failed) around:

    Application.CommandBars(“Standard”).FindControl(ID:=128).List(1)

    I’m assuming it is because it is clearing this list out when refreshing the pivots. Any suggestions on how to stop this from occurring?
    Cheers, Ryan

  3. Jeff Weir says:

    Hi Ryan. Sorry, missed this until now. I suggest you just put On Error Resume Next before that line, and On Error Goto 0 after. (Or On Error Goto Errhandler if you have an error handler set up). That’s what I did in the next installmnent of this series.

Posting code or formulas in your comment? Use [cc] tags!

  • [cc_vb]Block of code goes here[/cc_vb]
  • [cci_vb]Inline code goes here[/cci_vb]
  • [cc]Formula goes here[/cc]

Leave a Reply