Can I lock a worksheet and still be able to hide and show groups?

[Ryan]

I want to protect a worksheet but still be able to hide and show groups but
cant seem to figure out how. Is it possible to do?

 

[Gord Dibben]

Private Sub Worksheet_Activate()
With Me
.Protect Password:="justme", userinterfaceonly:=True
.EnableOutlining = True
End With
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code"

Copy/paste into that sheet module. Edit if desired the Alt + q to return to
the Excel window.


Gord Dibben MS Excel MVP

 

[Dave Peterson]

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

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)