Sheet Protection - Grouping

[carlyman]

I want to protect a worksheet; however, I don't want to lose the ability to collapse/uncollapse rows that I have grouped together.

What option needs to be checked when locking the sheet to allow this? Or, is it not possible?

Thanks,
JC

[Dave Peterson]

If you already have the outline 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
End With
End Sub

(I wasn't sure what caused your outlining--data|Group or Data|subtotals).)

It needs to be reset each time you open the workbook. (excel doesn't remember
it after closing the workbook.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

carlyman wrote:
>
> I want to protect a worksheet; however, I don't want to lose the ability
> to collapse/uncollapse rows that I have grouped together.
>
> What option needs to be checked when locking the sheet to allow this?
> Or, is it not possible?
>
> Thanks,
> JC
>
> --
> carlyman
> ------------------------------------------------------------------------
> carlyman's Profile: http://www.excelforum.com/member.php...o&userid=22440
> View this thread: http://www.excelforum.com/showthread...hreadid=469911

--

Dave Peterson

[carlyman]

Thanks Dave, but is it possible to do without using Macros? I try to avoid them as much as possible unless absolutely necessary.

I grouped the rows by going to "Data -> Group and Outline -> Group"

Again, thanks for your help.
JC

[Dave Peterson]

You can do it manually, but you'd still have to do it each time you open the
workbook.

Open the workbook.
hit alt-f8 (to get to the VBE -- where those macros live!)
hit ctrl-r (to see the project explorer -- like windows explorer)
find your workbook/project and click on it
hit the asterisk on the number keypad, not shift-8
this will expand all the levels of the project.

Look for your worksheet name and click on it
hit F4 to see its properties.

Look for the Enableoutlining property and change that to True.

======
The macro doesn't look too bad, huh?


carlyman wrote:
>
> Thanks Dave, but is it possible to do without using Macros? I try to
> avoid them as much as possible unless absolutely necessary.
>
> I grouped the rows by going to "Data -> Group and Outline -> Group"
>
> Again, thanks for your help.
> JC
>
> --
> carlyman
> ------------------------------------------------------------------------
> carlyman's Profile: http://www.excelforum.com/member.php...o&userid=22440
> View this thread: http://www.excelforum.com/showthread...hreadid=469911

--

Dave Peterson