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 . . .

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

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