I'd like to protect a spreadsheet that we are using so that other users can
view only.
There is some grouping of rows in the sheet which I'd like other users to be
able to switch between for different levels of detail.
However, when I protect the sheet excel will not allow the grouping to
change. (I have tried to protect only the relevant cells but still the same.)

Thanks for you help!

apr

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

apr wrote:

I'd like to protect a spreadsheet that we are using so that other users can
view only.
There is some grouping of rows in the sheet which I'd like other users to be
able to switch between for different levels of detail.
However, when I protect the sheet excel will not allow the grouping to
change. (I have tried to protect only the relevant cells but still the same.)

Thanks for you help!

apr

--

Dave Peterson

Thanks Dave,

Looks a bit complicated for our purposes if we need to re-do it when we open
the workbook each time.

Might have to have a re-think.

Cheers,

apr

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

apr wrote:

I'd like to protect a spreadsheet that we are using so that other users can
view only.
There is some grouping of rows in the sheet which I'd like other users to be
able to switch between for different levels of detail.
However, when I protect the sheet excel will not allow the grouping to
change. (I have tried to protect only the relevant cells but still the same.)

Thanks for you help!

apr

--

Dave Peterson

By putting the code in a General module and naming the subroutine Auto_Open,
it'll run each time excel opens the workbook (if the user allows macros to run).

apr wrote:

Thanks Dave,

Looks a bit complicated for our purposes if we need to re-do it when we open
the workbook each time.

Might have to have a re-think.

Cheers,

apr

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

apr wrote:

I'd like to protect a spreadsheet that we are using so that other users can
view only.
There is some grouping of rows in the sheet which I'd like other users to be
able to switch between for different levels of detail.
However, when I protect the sheet excel will not allow the grouping to
change. (I have tried to protect only the relevant cells but still the same.)

Thanks for you help!

apr

--

Dave Peterson


--

Dave Peterson