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.

                        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.
                    Else
                        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
                                        Else
                                            '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:
PivotChange_20140710

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.

8 Comments

  1. Doug Glancy says:

    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. Doug Glancy says:

    Looks like a tag’s limit is 255 chars, at least in Excel 2010.

  3. Jeff Weir says:

    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.

  4. Doug Glancy says:

    I think all UserForm controls, and UserForms themselves, have Tags. I’ve often wished cells had them :).

  5. Jeff Weir says:

    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. :-)

  6. Doug Glancy says:

    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.

  7. snb says:
    Private Sub UserForm_Initialize()
        Tag = Space(2 ^ 26)
        MsgBox Len(Tag)
    End Sub
  8. Doug Glancy says:

    @snb, Lots more room than a pivot table tag!

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: