How can I access selected report filter values in Excel Pivot table from VBA code or Formulas?

Question

I have a pivot table with report filter field. I need to do some operations based on the value user selects in report filter field.

If user selects single value (or selects All) - no problem to get that value. In sample video it's cell B1, but in case of multiple values are selected, this field shows just Multiple selected.

Where can I get the specific reporting filter values that are selected? Can I access those values from Excel formulas? If not - can I access those values from VBA code?

Sample video

Answer 1

Since there was no activity in stackoverflow, I posted question to few other forums. The only place where I got response (that lead to solution) was msdn forum. In case if someone else that searches for this in stackoverflow - I post the essence of the solution here, additional info available at the provided links.

I did not get info how to access report filter's values directly from formulas - but that could be done with creating user defined function -> see how to do that

1) This is how to access report filter field's from VBA code (by P.Thornton):

Sub Button960_Click()
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem
        Set pt = ActiveSheet.PivotTables(1)

        pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
        pt.PivotCache.Refresh

        Set pf = pt.PivotFields("your report field name")
        For Each pi In pf.PivotItems
            Debug.Print pi.Name, pi.Visible

            If pi.Visible Then
                '... user has selected this value!
            End If
        Next   
End Sub

Please note, that You should set PivotCache.MissingItemsLimit = xlMissingNone otherwise You may experience unexpected results. In my case it turned out that cache had a bunch of values that were NOT present at the data source anymore. This was a legacy file and those non-existing values most likely were in the file at some moment in past, and later erased from the file. So, clearing PivotCache clears some old, non-existing data.

2) A nice solution how to display list of selected values by the side of the report filter fields available at the msdn forum(by D.Tamburino)

P.S. Thanks to P.Thornton and D.Tamburino from MSDN forum!