Toggle between ShowDetail of pivot field in pivot table.

[rumshar]

Hi All,
In the attached file there is a pivot table, wherein I want to toggle between expand entirefields and collapse entire field(pivot field client) with the help of macro. In other words if a user clicks the button, pivot field(client) should expand all and if he/she clicks again it should collapse all the fields.

I tried with below code which didn't work. Can any one help me please?
Many thanks.
Rudra

Option Explicit

Sub ExpandCollapse()

Dim PT As PivotTable

Set PT = Sheets("Pivot").PivotTables(1)

If PT.PivotFields("Client").ShowDetail = False Then
    PT.PivotFields("Client").ShowDetail = True
End If

End Sub

 

[Hui]

Rumshar

Try this code instead:

Sub ExpandCollapse()
Dim pt As PivotTable, fld As PivotField, pvti As PivotItem
Set pt = ActiveSheet.PivotTables(1)
Set fld = pt.PivotFields("Client")
For Each pvti In fld.PivotItems
If pvti.RecordCount > 1 And pvti.ShowDetail = True Then
  pvti.ShowDetail = False
  Else
  pvti.ShowDetail = True

End If
Next
End Sub

 

[Luke M]

Looks like the ShowDetail property is attached to PivotItems not PivotFields. Here's an ammended code:

Sub ExpandCollapse()

Dim PT As PivotTable
Dim PF As PivotField
Dim showBool As Boolean
Dim PI As PivotItem

Set PT = Sheets("Pivot").PivotTables(1)
Set PF = PT.PivotFields("Client")

'Determine where we are already hidden or not
showBool = PF.PivotItems(1).ShowDetail

'Change all items to be either hidden or visible
For Each PI In PF.PivotItems
    PI.ShowDetail = Not (showBool)
Next PI

End Sub

 

[rumshar]

Thank you gentlmen,
Thank you very much, both the codes worked as expected.Have a nice day!

Regards
Rudra

 

[rumshar]

Hi All,
Also I was able to bypass loop by modifying it a little bit. My final code looks like:

Sub ExpandCollapse()

Dim PT As PivotTable
Dim PF As PivotField
Dim ShowBool As Boolean

Set PT = Sheets("Pivot").PivotTables(1)
Set PF = PT.PivotFields("Client")

ShowBool = PF.PivotItems(1).ShowDetail

If ShowBool = False Then
    PT.PivotFields("Client").ShowDetail = True
Else
    PT.PivotFields("Client").ShowDetail = False
End If

End Sub

 

[blake007]

Thank you!