Expanding and collapsing grouped rows, using VBA

[Gerald Higgins]

Hi all

I have a worksheet in which I have many groups set up.
This all works fine.
I would like to add a couple of buttons to give users the option to collapse all groups, and to expand all groups.
Can anyone help me with the code to do this please ?

I have done a search on this board, and found code that will CREATE the groups, but I don't need to do that.
I have also tried using the macro recorder while I collapse or expand a group, and no code seems to get recorded.
I'm using 2007.

Any ideas ?

Thanks in advance for any help !

 

[hippiehacker]

don't know if you still need it

ActiveSheet.Outline.ShowLevels RowLevels:=1 ' to collapse the rows
ActiveSheet.Outline.ShowLevels RowLevels:=2 ' to expand the rows

 

[taurean]

Hi all
:
I have also tried using the macro recorder while I collapse or expand a group, and no code seems to get recorded.
I'm using 2007.

Any ideas ?

Thanks in advance for any help !

I am also using Excel 2007. Funny thing I noticed is:

a. The macro recorder doesn't catch anything if you click on individual groups for showing / collapsing.

b. But if you click on the Level numbers then it records code like below:

    ActiveSheet.Outline.ShowLevels RowLevels:=2
    ActiveSheet.Outline.ShowLevels RowLevels:=1

as posted by hippiehacker!

 

[Gerald Higgins]

Awesome ! Got it working !

Thank you very much hippiehacker and Taurean !