Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 37
Default Group and Outline

Upon protecting a worksheet I find I am unable to collapse or expand grouped
rows in a worksheet.
Is there a way to accomplish this without unprotecting the worksheet? It is
used by 44 people and we need to have the cells with formulas protected?
Thanks for any help
Marsh
  #2   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 35,218
Default Group and Outline

If you already have the outline/subtotals/autofilter applied, you can protect
the worksheet in code (auto_open/workbook_open??).

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
.Protect Password:="hi", userinterfaceonly:=True
.EnableOutlining = True
'.EnableAutoFilter = True
End With
End Sub

It needs to be reset each time you open the workbook. (Earlier versions of
excel don't remember it after closing the workbook. IIRC, xl2002+ will remember
the allow autofilter setting under tools|Protection|protect sheet.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


Marsh wrote:

Upon protecting a worksheet I find I am unable to collapse or expand grouped
rows in a worksheet.
Is there a way to accomplish this without unprotecting the worksheet? It is
used by 44 people and we need to have the cells with formulas protected?
Thanks for any help
Marsh


--

Dave Peterson
  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 37
Default Group and Outline

Thank you.
The link was very helpful.

"Dave Peterson" wrote:

If you already have the outline/subtotals/autofilter applied, you can protect
the worksheet in code (auto_open/workbook_open??).

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
.Protect Password:="hi", userinterfaceonly:=True
.EnableOutlining = True
'.EnableAutoFilter = True
End With
End Sub

It needs to be reset each time you open the workbook. (Earlier versions of
excel don't remember it after closing the workbook. IIRC, xl2002+ will remember
the allow autofilter setting under tools|Protection|protect sheet.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


Marsh wrote:

Upon protecting a worksheet I find I am unable to collapse or expand grouped
rows in a worksheet.
Is there a way to accomplish this without unprotecting the worksheet? It is
used by 44 people and we need to have the cells with formulas protected?
Thanks for any help
Marsh


--

Dave Peterson

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sheet protection and "Group and Outline" OkieViking Excel Discussion (Misc queries) 9 May 22nd 07 05:17 PM
Group and Outline Cara Excel Discussion (Misc queries) 1 October 10th 06 01:53 AM
HOW TO CREATE DIFFERENT GROUP AND OUTLINE IN EXCEL Viswanathan Balaji Excel Worksheet Functions 0 April 25th 06 06:19 AM
Group & Outline and Recalculation BrianH Excel Discussion (Misc queries) 3 April 20th 06 09:19 AM
Group and Outline Settings -Problem Courtney Excel Discussion (Misc queries) 0 April 10th 06 10:00 PM


All times are GMT +1. The time now is 09:51 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"