What caused that PivotTableUpdate? Part Tres.

Okay, so:

  • In Part One, we considered the problem of determining what actually triggered a PivotTableUpdate (or it’s equally gormless twin, the PivotTableChangeSync event)
  • In Part Two we worked out that we could tell via the contents of the Undo Stack whether the update was caused by filtering, a refresh, a structure change, or other less common tweaks – and we determined that we still couldn’t tell what particular PivotFilter a user might have changed.

But you already know that.

Today, we’ll wrap this knowledge in a function that will determine what triggered a PivotTableUpdate event by reading and returning the related Undo Stack entry. If the update event was triggered by some hoodlum filtering the pivot, the function will *try* to determine which PivotField it was so that it can additionally return the name of that PivotField. I say *try* because there are no guarantees in life apart from death, taxes, and the fact that when my wife goes to sunny Spain for 6 weeks I get to stay behind in the Southern Hemisphere winter and pay for it. (Ah well…she took the kids, so we *both* get a holiday of sorts.)

So how are we going to do this? First, we’ll use that underwhelming PivotTableUpdate event as a trigger for our PivotChange function. In this case we’ll feed that PivotChange result to a messagebox, for instant feedback.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim strWhatHappenned As String
strWhatHappenned = PivotChange(Target)
If strWhatHappenned <> "" Then MsgBox strWhatHappenned
End Sub

Then in our actual function PivotChange, we’ll start by retrieving the most recent item from that Undo Stack.

Dim strLastUndoStackItem As String
Dim pf As PivotField
Dim i As Long
Dim strVisibleItems As String
Dim bIdentified As Boolean
Dim strElimination As String
Dim bElimination As Boolean

Application.EnableEvents = False

On Error Resume Next 'in case the undo stack has been wiped or doesn't exist
strLastUndoStackItem = Application.CommandBars("Standard").Controls("&Undo").Control.List(1)
On Error GoTo 0

The next thing we’re going to record how many visible items there are in each visible PivotField to a pipe-delimited string, using the pf.VisibleItems property:

If strLastUndoStackItem <> "" Then
For i = 1 To pt.VisibleFields.Count
Set pf = pt.VisibleFields(i)
With pf
If .Orientation <> xlDataField Then
If .Name <> "Values" Then
If pf.Orientation <> xlPageField Then
strVisibleItems = strVisibleItems & .Name & "|" & pf.VisibleItems.Count & "||"

…unless it’s a PageField, because stinky PageFields don’t have said pf.VisibleItems property. You can pf.VisibleItems them all you want, and all you’ll ever get back is “1”. God, I hate PageFields. So for PageFields we’ll record what that PageField’s filter currently displays. This will either say “(All)” or “(Multiple Items)”, or will contain the name of just one PivotItem in the event that it’s filtered to show just one PivotItem. UPDATE: We’ll also record whether “Select Multiple Items is checked, in case a user changes that setting but changes nothing else.

strVisibleItems = strVisibleItems & .Name & "|" & pf.LabelRange.Offset(, 1).Value & "|" & .EnableMultiplePageItems & "||"
End If
End If 'If .Name <> "Values" Then
End If 'If .Orientation <> xlDataField Then
End With
Next i

Note that we didn’t use the pf.CurrentPage to retrieve the name of that stinky PageField, because if the PageField has been set so that the user can select multiple items (i.e. EnableMultiplePageItems = True) then surprise surprise pf.CurrentPage always returns “(All)” even if just one item is selected. So instead we’ll use pf.LabelRange.Offset(, 1).Value to get the filter’s title. God, I hate PageFields.

Now we’ll check if that PivotTableUpdate was caused by a user filtering a PivotField.

Select Case strLastUndoStackItem
Case "Filter", "Select Page Field Item"

If it was, we’ll use that record of currently visible items we just put together. How? Well, we’re going to compare it against the number of items that were visible before the Pivot was refreshed. If there’s a difference for a particular field, then bing! we have our culprit. But where do we get those previous pf.VisibleItems stats from? Why, we stored them somewhere clever the last time this code ran…in the PivotTable itself!

PT Summary box 2

Ever wondered what pt.Summary did? Wonder no more…it allows you to read or write whatever you want to that “Description” box above. Here’s the code that does the reading and comparison:

With pt
If InStr(.Summary, "|") = 0 Then 'the Summary field contains NO previous info about pivot layout etc
PivotChange = "PivotFilter changed: Unable to determine which one." 'Ahh, but we will, next time.
If .Summary <> strVisibleItems Then
For i = 0 To UBound(Split(.Summary, "||"))
If Split(.Summary, "||")(i) <> Split(strVisibleItems, "||")(i) Then
PivotChange = "PivotFilter changed: " & Split(Split(.Summary, "||")(i), "|")(0)
bIdentified = True
Exit For
End If
Next i
End If 'If .Summary <> strVisibleItems Then

So far, we’ve got code that will pick up most instances of a PivotField being filtered. But not all, because our method so far relies on there being a change in the number of visible items in a PivotField. Meaning if some malicious filterer (filteree?) changes the filter selection whilst leaving the same number of things visible, then we’ll miss it.

What’s more, if the PivotField is a PageField and the filter text reads ‘Multiple Items’ both before and after the change, then because we were forced to rely on picking up changes in the actual filter’s titleg rather than the unhelpful .visibleitems property, we wouldn’t have noticed any change even if the user did leave a different amount of items visible before and after. Stinky PageFields. Ought to be ashamed of yourselves.

But even in both of those cases, we’ve got one last-ditch effort to identify the culprit with the information that we already have to hand. Can you think of it? No? Come on now…how often have I said to you:

When you have eliminated the impossible, whatever remains, however improbable, must be the truth.

That’s right…once. So pay attention, Sherlock. If we check all the visible fields to see if *just one of them alone* has neither .AllItemsVisible = True nor .EnableMultiplePageItems = false, then by process of elimination, this field must be the one that triggered the change, as changes to any of the others would have been identified in the code earlier.

Here’s Watson store:

If Not bIdentified Then
'Check all the visible fields to see if *just one of them alone* has
' neither .AllItemsVisible = True nor .EnableMultiplePageItems = false.
' If that's the case, then by process of eliminaiton, this field
' must be the one that triggered the change, as changes to any of the
' others would have been identified in the code earlier.
i = 0
For Each pf In pt.VisibleFields
With pf
If .Orientation <> xlDataField And .Name <> "Values" Then
If .AllItemsVisible = True Then
'it's not this field
bElimination = True
ElseIf .Orientation = xlPageField And .EnableMultiplePageItems = False Then
' it's not this field either
bElimination = True
'It *might* be this field
i = i + 1
strElimination = strElimination & .Name & ";"
End If 'If .AllItemsVisible = True The
End If 'If .Orientation <> xlDataField And .Name <> "Values" Then
End With
Next pf

If i = 1 Then
PivotChange = "PivotFilter changed: " & Left(strElimination, Len(strElimination) - 1) & "."
ElseIf bElimination = True Then PivotChange = "PivotFilter changed: one of " & Left(strElimination, Len(strElimination) - 1) & "."
Else: PivotChange = "PivotFilter changed: Unable to determine which one."
End If 'If i = 1 Then
End If 'If Not bIdentified Then
End If 'If InStr(.Summary, "|") = 0 Then
End With

And if the PivotTableUpdate wasn’t caused by filtering, then all we need do is tell the calling procedure what that action the user performed was:

Case Else: PivotChange = strLastUndoStackItem
End Select

All that’s left now is to overwrite the old record of visible fields in that PivotTable Alt Text box with the current record, so that next time the code runs we’ll have something to reminisce about:

pt.Summary = strVisibleItems
End If 'If strLastUndoStackItem <> ""
Application.EnableEvents = True
End Function

So there we have it: a function that lets you know what triggered a PivotTableUpdate event and that does it’s *utmost* to let you know which PivotFilter was changed, if any.

Here’s a sample file with it all good to go:

Sure, it will still not capture cases where malicious filterers (filterees?) leave the same number of things visible, or where any stinky PageField filter read ‘Multiple Items’ both before and after the change. But it will warn you if that happens, and it’s not like that’s gonna happen every time.

And besides, in our upcoming season finale I’ll introduce an additional function that will do a far deeper, resource-intensive dive in that particular case.

Till then, Hasta Pronto.

9 thoughts on “What caused that PivotTableUpdate? Part Tres.

  1. Wow Jeff! Very cool behind-the-scenes hacking.

    Since nobody much messes with their Standard toolbar that technique seems pretty reliable. Even more trusty would be to create a temporary commandbar and add the Undo control to it.

    Instead of the pivot table’s summary property, I think you could just use its Tag property, which is designed for that kind of thing. It’s not visible to users, so might be more foolproof. I don’t know if there’s a character limit for a tag.

  2. Hi Doug. Thanks for the comment…for a while I thought my first comment on this post was going to be my mother. Hey, I hadn’t heard about the Tag property. Awesome. I wonder what else has a tag. Apart from my trailer out on the road, which has about 5 tags from different taggers.

  3. Yeah, I just was playing around in the VBA Object Browser and see that apart from PivotTables, looks like only CommandBar buttons, Comboboxes,and controls have a Tag property. How come you knew about it?

    I don’t know much about commandbars at all. I’ll have to look further into your suggestion re creating a temporary commandbar and add the Undo control to it. Or I’ll have to look at the blog post you’re about to write on it. :-)

  4. I’m not sure when I learned about the Tag property. It’s been a while. There’s also a Parameter property for Commandbar controls that’s similar.

    Until I write that post you could take a look at the VBA in my MenuRighter addin (or my ancient FaceIdViewer addin). Lots of temporary toolbars used in interesting ways in those, if I do say so myself.

  5. @Doug: Re your comment “I think all UserForm controls, and UserForms themselves, have Tags. I’ve often wished cells had them :).”

    Cells do have ’em. Under another name.
    Activecell.ID = “Hello World”
    ? Activecell.ID
    Hello World

    But they don’t persist between sessions. That doesn’t stop you storing helpful things in them for the current session. Charles Williams uses ’em to cache lookup stuff in https://fastexcel.wordpress.com/2011/07/22/developing-faster-lookups-part-2-how-to-build-a-faster-vba-lookup/

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.