Open / Close of all Groupings...

  • Thread starter Thread starter Cade
  • Start date Start date
C

Cade

Would anybody be able to provide me with a hint or even the code how I
can produce a marco that constantly runs in the backgroup (so probably
the personal.xls) that opens ALL groupings (rows/columns) when hitting
lets say F2 and close all groupings when hitting it again.
Unfortunately, Excel doesn't allow to hide groupings when several
sheets are selected, so a quick F-key that basically allows me to
avoid using the mouse and click on the "1" for columns and rows would
be amazing.

Thanks for your help,

Cade
 
This seemed to work ok for me--although I don't think I'd use the F2 key. I use
that key to go into edit mode to change a cell.

If you don't want to add the code to your personal.xls file:

Create a new workbook and then add this code into a general module. Then save
that workbook as an addin and save it in your XLStart folder.

Option Explicit
Sub testme01()
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
wks.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1
Next wks
End Sub
Sub testme02()
Dim wks As Worksheet
Dim i As Long
For Each wks In ActiveWorkbook.Worksheets
With wks
.Outline.ShowLevels RowLevels:=8, ColumnLevels:=8
If .AutoFilterMode Then
If .FilterMode Then
.ShowAllData
End If
End If
End With
Next wks
End Sub
Sub workbook_open()
Application.OnKey "{F2}", "testme01"
Application.OnKey "+{F2}", "testme02"
End Sub
Sub workbook_close()
Application.OnKey "{F2}"
Application.OnKey "+{F2}"
End Sub

(In minor testing, it looked like you could only have 8 levels of outlining. If
you can have more, change the code accordingly.)
 
Back
Top