VBA Button to Expand All / Collapse all groupings

[zach9208]

I need a VBA Button that will toggle between "Expand All" and "Callapse All" for the groupings on a given worksheet. Any help would be appreciated. Thanks!!!

 

[Jeffrey Mahoney]

You can apply any of these short macros to your needs.

Sub ExpandAllColumns()    
  ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=8
    
End Sub


Sub CollapseAllColumns()
    
  ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1
    
End Sub


Sub CollapseAllRows()
    
  ActiveSheet.Outline.ShowLevels RowLevels:=1
    
End Sub




Sub ExpandAllRows()
    
  ActiveSheet.Outline.ShowLevels RowLevels:=8
    
End Sub

 

[zach9208]

I am looking for a code that I can put under one button. The first time I hit the button it should show. The second time I hit the button it should hide. Is this even possible?

 

[Jeffrey Mahoney]

Yes. Tell me which single column is in any group.

 

[Jeffrey Mahoney]

You can replace the column A with any column that would be hidden under your groups

Sub ToggleGroupExpand()  
If Range("A:A").EntireColumn.Hidden = True Then
    ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=8
  Else
    ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1
  End If
End Sub

 

[Jeffrey Mahoney]

This would only test one column. IF you wanted to include several columns in the mix:

Sub ToggleGroupExpand()  
If Range("A:A").EntireColumn.Hidden = True Or _
    Range("H:J").EntireColumn.Hidden = True Or _
    Range("M:N").EntireColumn.Hidden = True Then
    ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=8
  Else
    ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1
  End If
End Sub

 

[zach9208]

Ended up figuring it out using some of your code. I ended breaking it up into a total of 3 subs. The first two subs collapse and expand and the last sub allows me to create 1 button to collapse/expand. Thanks all for the suggestions!

Sub CollapseAll()
  Sheet2.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1
End Sub
Sub ExpandAll()
  Sheet2.Outline.ShowLevels RowLevels:=8, ColumnLevels:=8
End Sub


Sub ShowHideColumnsRows()
If ActiveSheet.Shapes("Button 3").TextFrame.Characters.Text = "Hide Groupings" Then
    ActiveSheet.Shapes("Button 3").TextFrame.Characters.Text = "Show Groupings"
    Call CollapseAll
Else
    ActiveSheet.Shapes("Button 3").TextFrame.Characters.Text = "Hide Groupings"
    Call ExpandAll
    ActiveSheet.Range("A1").Select
End If
End Sub

 

[Jeffrey Mahoney]

I'm glad you could adapt the code.