Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Grouping and Outlining in Excel using sheet protection
I have groups in my Spreadsheet. BUT, I'd also like to protect while allowing
other users to "Show Detail" and fill in some of the cells. Is it possible to protect a Worksheet and allow users to expand groups and make changes? Thank you . . . |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Grouping and Outlining in Excel using sheet protection
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 'If .FilterMode Then ' .ShowAllData 'End If 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, but that won't help when you're filtering via code.) Rich wrote: I have groups in my Spreadsheet. BUT, I'd also like to protect while allowing other users to "Show Detail" and fill in some of the cells. Is it possible to protect a Worksheet and allow users to expand groups and make changes? Thank you . . . -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Grouping and Outlining in Excel using sheet protection
Thank you very much Dave . . . it worked like a charm. (And, although my know
of VBA is very limited, I understand it!) Thanks again! Rich "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 'If .FilterMode Then ' .ShowAllData 'End If 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, but that won't help when you're filtering via code.) Rich wrote: I have groups in my Spreadsheet. BUT, I'd also like to protect while allowing other users to "Show Detail" and fill in some of the cells. Is it possible to protect a Worksheet and allow users to expand groups and make changes? Thank you . . . -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
protection and grouping | Setting up and Configuration of Excel | |||
outlining / Grouping | Excel Discussion (Misc queries) | |||
Outlining in Protected Sheet - Attention DP | Excel Discussion (Misc queries) | |||
Sheet Protection - Grouping | Excel Discussion (Misc queries) | |||
Excel grouping & protection | Setting up and Configuration of Excel |