Button to collapse / expand grouped rows

[Davin21]

Hi All,
I'm using excel 2007 and have just discovered the grouping rows function (that does not use a pivot table - i.e. data > group > group) - which is awesome

I have noticed that there are 2 buttons on the left of the screen when using this (1 / 2), where clicking on '1' collapses all groups and '2' expands all groups.

I would like to be able to assign a macro to a button to perform these two functions (so two buttons, one labelled 'Expand Groups' and the other 'Collapse Groups') so that it is easier for my end users to use?

I am no expert in creating VBA code though, and so I don't know where i'd even begin with this.....

Thanks!

 

[Datsmart]

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

Sub ChangeOutlineLevels2()
    ActiveSheet.Outline.ShowLevels RowLevels:=2
End Sub

I like the +/- sign at the Top of the Group. Follow these steps to change it from Default.

To change the location of the Grouping +/- sign from the bottom of the Group to the Top:
In the Data Ribbon, open the Outline Dialog box.
Click the small dropdown arrow in the lower right corner of the Data/Outline section.
Uncheck the "Summary Rows Below Detail" checkbox.

 

[Davin21]

Fantastic - thanks!

 

[breba]

Hi,

I had a similiar problem and came across this thread.
Like Davin21 mentioned, it's FANTASTIC! ....however I am also protecting the spreadsheet and when I click the button, this alert pops up

"You cannot use this command on a protected sheet. To use this command you must first unprotect the sheet......"

There is a "Debug" button as well and it takes me to the codes.

I have NEVER used a macro before, this is the first time I have researched and attempted to use it. is there a way I can make the buttons work even if my worksheet is protected??

Thanks

 

[Toan75]

Hi,

I know this thread is old but it's similar to what I am looking for. Can someone combine the above code under one button instead of two? Thanks!