What caused that PivotTableUpdate? Episode IV
So if you’ve been merrily following along, then you’ll know that we’ve got a function that picks up which PivotField just got filtered in a PivotTable, with a couple of exceptions:
- If users change the PivotFilter list, but leave the same count of things visible, or
- If any stinky PageField filters read ‘Multiple Items’ both before and after the change.
We’re about to add something more to our previous function to handle those cases.
With a few very minor exceptions, the majority of our previous function remains unchanged, and looks like so:
Dim strLastUndoStackItem As String
Dim pf As PivotField
Dim pi As PivotItem
Dim i As Long
Dim lngVisibleItems As Long
Dim lngFields As Long
Dim strVisibleItems As String
Dim bIdentified As Boolean
Dim strElimination As String
Dim bElimination As Boolean
Dim dicFields As Object ‘This holds a list of all visible pivotfields
Dim dicVisible As Object ‘This contains a list of all visible PivotItems for a pf
Dim varKey As Variant
Application.EnableEvents = False
On Error Resume Next ‘in case the undo stack has been wiped or doesn’t exist
strLastUndoStackItem = Application.CommandBars("Standard").FindControl(ID: = 128).Control.List(1)
On Error GoTo 0
If strLastUndoStackItem <> "" Then
For i = 1 To pt.VisibleFields.Count
Set pf = pt.VisibleFields(i)
With pf
If .Orientation <> xlDataField And .Name <> "Values" Then
If .Orientation <> xlPageField Then
strVisibleItems = strVisibleItems & .Name & "|" & .VisibleItems.Count & "||"
Else
‘pf.VisibleItems.Count doesn’t work on PageFields
‘So for PageFields we’ll record what that PageField’s filter currently displays.
strVisibleItems = strVisibleItems & .Name & "|" & .LabelRange.Offset(, 1).Value & "|" & .EnableMultiplePageItems & "||"
End If
End If
End With
Next i
Select Case strLastUndoStackItem
Case "Filter", "Select Page Field Item", "Slicer Operation"
With pt
If InStr(.Summary, "|") > 0 Then ‘the Summary field contains previously recorded info about pivot layout etc
If .Summary <> strVisibleItems Then
For i = 0 To UBound(Split(.Summary, "||"))
If Split(.Summary, "||")(i) <> Split(strVisibleItems, "||")(i) Then
PivotChange_GetFilterName = "PivotFilter changed: " & Split(Split(.Summary, "||")(i), "|")(0)
bIdentified = True
Exit For
End If
Next i
End If ‘If .Summary <> strVisibleItems Then
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 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.
lngFields = 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
lngFields = lngFields + 1
strElimination = strElimination & .Name & ";"
End If ‘If .AllItemsVisible = True The
End If ‘If .Orientation <> xlDataField And .Name <> "Values" Then
End With
Next pf
If lngFields = 1 Then
PivotChange_GetFilterName = "PivotFilter changed: " & Left(strElimination, Len(strElimination) – 1)
bIdentified = True
Else
But now, to track down those exceptions, we’re going to have to work some magic. Or as I put it in the code:
‘ We will have to use *The Force*
‘ =================================
For each visible PivotField, we’re going to compare the list of what is visible now to what was visible before. Bear in mind that we don’t know in advance what PivotTable the user is going to tamper with. Given this, you may be asking yourself:
You’re dead right, Golden Rod. And that will require us to either keep a duplicate of every PivotTable in the workbook, or to extract every single pivotitem in the entire workbook to a Dictionary or Array that we would have to continually update.
Unless we use the Force. Yes, let’s use the Force. After all, it is all around us.
With the help of The Force:
- We’re only going to record the settings for the PivotTable that just changed
- Then we’re going to programatically hit the Undo button
- Then we’re going to play spot-the-difference between what things looked like after the user made that change:
…and what it looked like before they made that change:
- Then we’re going to restore things back to the way the user wanted them:
To do this, we’re going to employ a Dictionary.
Of Dictionaries.
Like so:
‘ The If statement above is purely there to catch the possibility that
‘ we failed to find the filter in the above code because it’s the first
‘ time we’ve run the code, meaning nothing was previously stored in pt.summary
‘Create master dictionary
Set dicFields = CreateObject("Scripting.Dictionary")
‘Cycle through all visible pivotfields, excluding totals
For i = 0 To lngFields – 1
‘Create dicVisible: a dictionary for each PivotField that contain visible PivotItems
Set dicVisible = CreateObject("Scripting.Dictionary")
Set pf = pt.PivotFields(Split(strElimination, ";")(i))
With pf
If .Orientation <> xlPageField Then
For Each pi In .VisibleItems
With pi
dicVisible.Add .Name, .Name
End With
Next pi
Else:
‘Unfortunately the .visibleitems collection isn’t available for PageFields
‘ e.g. SomePageField.VisibleItems.Count always returns 1
‘ So we’ll have to iterate through the pagefield and test the .visible status
‘ so we can then record just the visible items (which is quite slow)
For Each pi In .PivotItems
With pi
If .Visible Then
dicVisible.Add .Name, .Name
End If
End With
Next pi
End If ‘If .Orientation = xlPageField Then
‘Write dicVisible to the dicFields master dictionary
dicFields.Add .Name, dicVisible
End With
Next i
Great, so we know what’s visible now. But what about before?
Well, in keeping with the Star Wars theme, anyone fancy a prequel?
And now we’ll check how the old compares to the new:
Set pf = pt.PivotFields(varKey)
Set dicVisible = dicFields.Item(varKey)
‘Test whether any of the items that were previously hidden are now visible
If pf.Orientation <> xlPageField Then
For Each pi In pf.VisibleItems
With pi
If Not dicVisible.exists(.Name) Then
bIdentified = True
PivotChange_GetFilterName = "PivotFilter changed: " & pf.Name
Exit For
End If
End With
Next
Else ‘pf.Orientation = xlPageField
lngVisibleItems = dicVisible.Count
i = 0
For Each pi In pf.PivotItems
With pi
If .Visible Then
If Not dicVisible.exists(.Name) Then
bIdentified = True
PivotChange_GetFilterName = "PivotFilter changed: " & pf.Name
Exit For
Else: i = i + 1 ‘this is explained below.
End If ‘If Not dicVisible.exists(.Name) Then
End If ‘If .Visible Then
End With
Next
‘ For non-PageFields, we know that the number of .VisibleItems hasn’t changed.
‘ But we *don’t* know that about Pagefields, and an increase in the amount of
‘ .VisibleItems won’t be picked up by our Dictionary approach.
‘ So we’ll check if the overall number of visible items changed
If Not bIdentified And i > lngVisibleItems Then
bIdentified = True
PivotChange_GetFilterName = "PivotFilter changed: " & pf.Name
Exit For
End If
End If ‘If pf.Orientation <> xlPageField Then
If bIdentified = True Then Exit For
Next
Great, we’ve found it. Now it’s time for operation Application.Redo!
WAIT! There is no Application.Redo method!
Aw, screw it…let’s use that Force stuff again:
Application.CommandBars("Standard").FindControl(ID:=129).Execute
…and then set the Hyperdrive for home:
End If ‘If lngFields = 1 Then
End If ‘If Not bIdentified Then
End If ‘If InStr(.Summary, "|") = 0 Then
End With
Case Else: PivotChange_GetFilterName = strLastUndoStackItem
End Select
End If ‘If strLastUndoStackItem <> ""
pt.Summary = strVisibleItems
Application.EnableEvents = True
‘Cleanup
Set dicFields = Nothing
Set dicVisible = Nothing
End Function
Stormtrooper: Let me see your sample file.
Obi-Wan: [with a small wave of his hand] You don’t need to see his sample file.
Stormtrooper: We don’t need to see his sample file.
Obi-Wan: These aren’t the sample files you’re looking for.
Stormtrooper: These aren’t the sample files we’re looking for.
Obi-Wan: He can go about his pivoting.
Stormtrooper: You can go about your pivoting.
Obi-Wan: Move along.
Stormtrooper: Move along… move along.
Okay, okay…here’s the sample file.
Can you stop remotely squeezing my throat now, Darth?
PivotChange_20140802
Update
I have re-factored the code in the sample file as per the post at Broken Arrow.