I have grouped some columns together and have protected the worksheet. I am
wanting to be able to use the show/hide details commands without having to
remove the protection from the worksheet as this will be a shared document.
Does anybody have any ide how this can be achieved, i am using Excel 2007.

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

kjl38 wrote:

I have grouped some columns together and have protected the worksheet. I am
wanting to be able to use the show/hide details commands without having to
remove the protection from the worksheet as this will be a shared document.
Does anybody have any ide how this can be achieved, i am using Excel 2007.

--

Dave Peterson

Unfortunately this does not appear to help as it still does not allow the
Show/hide detail option. I receive a message requesting that the protection
is removed from the worksheet. As this is going to be used by other users I
do not want them to be able to alter any formatting, however i do want them
to be able to hide grouped rows and columns to make the spreadsheet more user
friendly. Any ideas!

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

kjl38 wrote:

I have grouped some columns together and have protected the worksheet. I am
wanting to be able to use the show/hide details commands without having to
remove the protection from the worksheet as this will be a shared document.
Does anybody have any ide how this can be achieved, i am using Excel 2007.

--

Dave Peterson

I think you didn't do it correctly.

Maybe you didn't use the correct worksheet name. Maybe you didn't use the the
correct password. Maybe you didn't put the code in the correct location.

Try it again.

kjl38 wrote:

Unfortunately this does not appear to help as it still does not allow the
Show/hide detail option. I receive a message requesting that the protection
is removed from the worksheet. As this is going to be used by other users I
do not want them to be able to alter any formatting, however i do want them
to be able to hide grouped rows and columns to make the spreadsheet more user
friendly. Any ideas!

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

kjl38 wrote:

I have grouped some columns together and have protected the worksheet. I am
wanting to be able to use the show/hide details commands without having to
remove the protection from the worksheet as this will be a shared document.
Does anybody have any ide how this can be achieved, i am using Excel 2007.

--

Dave Peterson


--

Dave Peterson