Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Mar 2006
    Posts
    76
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Macro to expand and collapse

    I have a macro to place into a text box, to expand and collapse a pivot table, how do i modify so that, the first click expands and the next click collapses, or vice versa?

    Sub Format_Collapse_and_expand_Pivot()
    ' Format_Collapse_and_expand_Pivot Macro
    ActiveSheet.PivotTables("PivotTable1").PivotFields ("DESCRIPTION"). _
    ShowDetail = True
    ActiveSheet.PivotTables("PivotTable1").PivotFields ("DESCRIPTION"). _
    ShowDetail = False
    End Sub

    Any help would be greatly appreciated
    Thanks!!

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi

    This is a common requirement.
    To toggle any setting (e.g. display/hide specified columns) with a single vba routine, you fetch the current setting, and then use the NOT operator to switch it:
    Code:
    Sub Format_Collapse_and_expand_Pivot()
    
    zVisible =  ActiveSheet.PivotTables("PivotTable1").PivotFields("DESCRIPTION"). _
     ShowDetail 
    ActiveSheet.PivotTables("PivotTable1").PivotFields("DESCRIPTION"). _
    ShowDetail = not zVisible 
    
    End Sub
    zeddy

  3. #3
    Star Lounger
    Join Date
    Mar 2006
    Posts
    76
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Thanks Zeddy
    i applied it and i need an additional code that would expand on the first click then contract on the second click. as i am applying this to a text box as control box for those not familiar with pivot table comtrols

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •