- 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
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)
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 'If .Name <> "Values" Then
End If 'If .Orientation <> xlDataField Then
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!
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:
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
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
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
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
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
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
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.