VBA for finding filters and hidden items in a pivot column

[macfuller]

I am trying to identify VBA code to tell me if pivot table columns have a filter on them, either value/label or by selected items. It's soooo easy to forget you set a filter, there's a tiny icon change on the column that could be a sort, could be a filter, so I just want something to tell me if I've left the engine running.

It's easy enough to hide something if it's visible, but to find something that's already hidden is tough.

In the image below I have unchecked two of the values in the list. So, I would like to know that the column has been filtered.

This is a Power Pivot table from the data model. It seems the PivotFilters don't enumerate so you have to reference the filter directly. The code below works to see if there is a value or label pivot filter on the selected field using ptFld.PivotFilters(1).FilterType

PivotItems can enumerate but they only show what's visible, so I can't check each item's .Visible property for xlHidden since only the visible show up! I tried the ptFld.HiddenItemsList property but this always returns a null string.

How can I tell that I'm missing Custom Interfaces and EDI from my pivot table results? I don't even need the strings listed, I'll just take a Boolean value that not everything is showing...

    Dim pt As PivotTable
    Dim ptCube As CubeField
    Dim ptFld As PivotField
    Dim ptFltr As PivotFilter
    Dim ptItem As PivotItem
   
    Dim iFiltered As Integer
   
    Dim arrV As Variant
   
    Dim dict As Dictionary
    Set dict = Dictionary_Create_PivotFilterType()
   
    Dim strFilters As String
    strFilters$ = vbNullString

    For Each pt In ActiveSheet.PivotTables
        For Each ptFld In pt.PivotFields
'   PageField values are in the top filter section
'       Decide if you want to include those in the list

'   PivotItems lists selected values whether or not they're filtered - however many show
'   PivotFilters do not appear to enumerate - you have to reference directly

'   Is it filtered?
            On Error Resume Next
            If ptFld.name <> "Values" Then
                Select Case ptFld.Orientation
                    Case xlHidden, xlPageField
                    Case Else
'   This code checks to see if a value or label filter is applied, but it doesn't work for selecting values from a checkbox
                        iFiltered% = 0
                        iFiltered% = ptFld.PivotFilters(1).FilterType
                        If iFiltered% > 0 Then
                            If strFilters$ = vbNullString Then
                                strFilters$ = pt.name & " || " & ptFld.name & ": " & Dictionary_RetrieveValue(dict, iFiltered%)
                            Else
                                strFilters$ = strFilters$ & vbLf & pt.name & " || " & ptFld.name & ": " & Dictionary_RetrieveValue(dict, iFiltered%)
                            End If
'                            Debug.Print ptFld.name & ": " & Dictionary_RetrieveValue(dict, iFiltered%)
                        End If

'   Now we need to look at each pivotitem to see if it's hidden.
'       again, the enumeration only appears to go through visible items

                        arrV = ptFld.HiddenItemsList

'  This always returns a null string

                End Select
            End If
        Next ptFld
    Next pt