Collapse grouped rows and columns on opening workbook

[Scott]

Hi, I have a large share workbook of some 22 sheets. Every sheet has a couple
of sections of grouped rows and columns. I am looking for way to ensure that
when ever a user opens the workbook, either directly or on sharepoint, that
the grouped rows and columns are ‘collapsed’.

 

[Andrea Jones]

Press Alt+F11 to bring up the VBA editor and click on ThisWorkbook in the
Project pane. Select Workbook from the drop-down list at the top of the
editor window and Open from the right-hand list and you will see the Private
Sub Workbook_Open() procedure appear. Add the following code:

Private Sub Workbook_Open()
Worksheets("Sheet1").Outline.ShowLevels 1
End Sub

(you can add more lines for other sheets containing outlines).

Andrea Jones
www.stratatraining.co.uk

 

[Scott]

Hi thanks for the reply. I should have mentioned that the worksheets have
variable names, depending on the input in range A10:A30 on the main sheet
called "Cover Page". Also working in Excel 2003 in case it makes a difference.

 

[Andrea Jones]

OK, use this:

Private Sub Workbook_Open()
Dim sname as string
For Each ws in Worksheets
sname=ws.name
ActiveWorkbook.Worksheets(sname).Outline.Showlevels 1
Next ws
End Sub

Andrea Jones
www.stratatraining.co.uk

 

[Scott]

Andrea,

Thanks for the code. I needed to mod it slightly to work, added parameter
for rows and columns level 1 at the end of the show level object. Otherwise
it only collapsed the rows? Thanks again

Scott