Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Collapse grouped rows and columns on opening workbook
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Collapse grouped rows and columns on opening workbook
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" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Collapse grouped rows and columns on opening workbook
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" wrote: 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" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Collapse grouped rows and columns on opening workbook
OK, use this:
Private Sub Workbook_Open() Dim sname as string For Each ws in Worksheets sname=ws.name ActiveWorkbook.Worksheets(sname).Outline.Showlevel s 1 Next ws End Sub Andrea Jones www.stratatraining.co.uk "Scott" wrote: 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" wrote: 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" wrote: 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Collapse grouped rows and columns on opening workbook
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 "Andrea Jones" wrote: OK, use this: Private Sub Workbook_Open() Dim sname as string For Each ws in Worksheets sname=ws.name ActiveWorkbook.Worksheets(sname).Outline.Showlevel s 1 Next ws End Sub Andrea Jones www.stratatraining.co.uk "Scott" wrote: 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" wrote: 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" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Expand/Collapse Grouped Data in Protected Worksheet | Excel Discussion (Misc queries) | |||
Shortcut for hide/show detail in grouped columns/rows | Excel Discussion (Misc queries) | |||
How do I move the expand/collapse for rows and columns in Excel? | Excel Worksheet Functions | |||
Grouped columns & rows | Excel Worksheet Functions | |||
Grouped Columns/Rows | Excel Worksheet Functions |