Search code examples
vbaexcelpivot-tablepivotitem

VBA - If PivotItem is Visible

I have some vba code which I would like to run if a PivotItem is visible and another piece of code to run if it isn't visible. When I run this sub and the item is switched on it doesn't recognise that it is visible.

Sub test()

If Sheets("Sheet1").PivotTables("PivotTable1").PivotFields("TestField").PivotItems("TestItem").Value = xlOn Then
MsgBox "is on"
Else: MsgBox "is off"
End If

End Sub

Cheers

Solution

  • You want the visible property not the value property of the pivotitem.

    Try this:

    If Sheets("Sheet1").PivotTables("PivotTable1").PivotFields("TestField").PivotItems("TestItem").Visible = True Then