Over at MSDN, ToWIZ writes:
I’m looking for a detailed guide that would help users understand the details of PivotTable events and their functioning. The documentation has only one sentence for each event. Surely Microsoft doesn’t consider this to be a detailed guide?
Apparently so.
Microsoft:
- The PivotTableUpdate event occurs after a PivotTable report is updated on a worksheet.
- The PivotTableChangeSync occurs after changes to a PivotTable. This event can be used only in Excel 2010 projects.
And let me get this straight: reading between the lines on this extensive documentation, you’re telling me that the new event does the same thing as the old event, except for the fact that it only works in Excel 2010 or later? Wow. Quite some improvement.
I can’t find a single thing that differentiates these two events – either on the web or in practice. Here, you try. Put this in a Sheet Module where you’ve got some pivots, and play around with them:
Private Sub Worksheet_PivotTableChangeSync(ByVal Target As PivotTable)
Debug.Print "PivotTableChangeSync: " & now()
End Sub
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Debug.Print “PivotTableUpdate: ” & now()
End Sub
Did you find a single case where one got triggered and the other didn’t? Me neither.
— UPDATE —
Eric van Rooijen did: Ticking the ‘Defer Layout Update’ option in the PivotFilter Pane means that only the Worksheet_PivotTableChangeSync gets triggered when changing PivotTable Fields.
You’ve almost got to wonder if someone on the team at Redmond misheard the brief for that 2010 addition. I’m sure they meant to introduce a new handler called PivotFieldChangeSync that would allow us to do something that we currently can’t – work out exactly what a user was doing to a PivotField (e.g. filtering a particular field). Instead we got this rehash with less scope than the old thing, as near as I can tell.
Which is a pity. Because PivotTables are the best thing about Excel, and PivotFilters (and now Slicers) are how users interact with that best thing. And here we are in Excel 2013 with still no good way to find out what PivotFilter a user just changed.
Why would you want to know that? Plenty of reasons…one of which is so that then you could efficiently sync a whole bunch of pivots in a dashboard in the case that slicers aren’t an option (e.g. the pivots are on different caches, or users have an earlier version of Excel that doesn’t support slicers). In that scenario, relying on just one of the above event handlers to run the code is damned inefficient, because:
- It would get triggered by any old change that you make to a Pivot.
- Even when it does get triggered by someone actually changing a PivotFilter, the question remains…which one did they change? Those events won’t tell you…
Don’t go thinking that a Worksheet_Selection change is going to help you work it out, because clicking on a PivotFilter or Slicer does not change your selection. And don’t go thinking you can use a Worksheet_Change event to capture the filter refreshing neither…when a PivotTable gets updated, the whole PivotTable gets updated, meaning that Worksheet_Change event returns the entire range that a PivotTable occupies – not just the cell behind the filter that a user just changed.
All this means that if you wanna say sync a whole bunch of pivots – and slicers aren’t an option – then relying solely on one of those insensitive Update events alone means you’ll have no choice but to sync pretty much every visible field that appears in the master pivot…something that may result in unacceptable delays to users if there are lots of pivots with lots of fields and with lots and lots of pivotitems in them. It could take many many minutes to iterate through all of them.
Sure, you can build some sort of userform control or array of shapes (one for each pivotfield) that the user clicks before they say filter a particular field. But that sounds complicated and quite likely bespoke to the dashboard concerned.
Or perhaps you could programatically put some invisible shapes in front of each PivotFilter, so that when when the user goes to click the PivotFilter they actually click those shapes instead, meaning you can then use application.caller to work out which shape – and therefore which PivotFilter – they clicked on. But you’d have to use SendKeys to open the filter that they just thought they clicked on, and you’d have to ensure those invisible boxes always moved with the PivotTable. Pretty invasive and unreliable stuff, really. And pretty complicated to set up.
Screw that…how ’bout a generalised solution that works right out of the box on any pivot, with no setup whatsoever?
How would you capture changes to specific PivotFields and the like?
Got a good method? Let us know in the comments. Got no idea? Tune in next time to see my approach.
