Testing for PivotItems.Visible in a Pivot Table Page Field

[ProdOps]

I am trying to work through the VBA syntax to check the Visible property of a group of PivotItems in a Page Field of a Pivot Table.

With the VBA code below I can accurately test the Visible property of a ROW field but testing the Visible property of the PAGE field returns FALSE in every instance. Whether an individual box is checked and displayed or if there are multiple selections checked.

The ultimate goal is to load all the Visible field names from the Page field into a test string that can be displayed in a cell. (AZ, TX, PA, etc&)

Any suggestions or
can someone verify that testing the Visible property of a Page field is not possible?
Thanks!

This code works great for Row fields but not for Page fields

Sub ChkPvtItems()
Dim pi As PivotItem
For Each pi In Worksheets("Report").PivotTables(1).PivotFields("City").PivotItems
If pi.Visible = True Then
MsgBox pi.Name & " is Visible"
Else
MsgBox pi.Name & " is NOT Visible"
End If
Next pi
End Sub

 

[Jerry_P]

Folderol - from EE solved this question.

The Pivot Table version is the key to making this work.
xlPivotTableVersion10 - Excel 2003 (& 2007 Compatibility Mode) - NO
xlPivotTableVersion12 - Excel 2007 (xlsx / xlsm / etc..) - YES

You can test for a Page field's PivotItems.Visible with a xlPivotTableVersion12 Pivot Table but not in a xlPivotTableVersion10 Pivot Table.

The macro below displays the string of visible items from the Page field "States" - below the Page Item box of the Pivot Table.

Many thanks to Folderol for the solution to this question...



</PRE>Sub ChkPvtPageItems()
Dim pi As PivotItem
Dim pf As PivotField
Dim states As String
On Error GoTo ErrorHandler
Set pf = Worksheets("Sheet4").PivotTables(1).PivotFields("ST")
For Each pi In pf.PivotItems
' ActiveSheet.PivotTables("PivotTable1").PivotFields("ST").CurrentPage = pi.Name
If pi.Visible = True Then
states = states & IIf(Len(states) > 0, ",", "") & pi.Name
End If
Next pi
'MsgBox states
Range("B5").Select
ActiveCell.FormulaR1C1 = ""
ActiveCell.FormulaR1C1 = "State Selection is: " & states
Exit Sub

ErrorHandler:
MsgBox pi.Name & " is NOT Visible"
Resume Next
End Sub