How to protect a shared sheet but allow groups to expand

[Don Muller]

Hi,

I am running Office XP and I am having a problem with protection and groups.
On one of my sheets I have grouped some rows so that the plus/minus sign is
on the left. Some of the cells are unprotected so they can be updated when
the sheet is protected. The problem is that when the sheet is protected you
cannot expand or collapse the groups. I found some code on Google that runs
when you open the workbook and allows the groups to function properly and
the codes works great. However when I share the workbook the code no longer
functions and in fact generates an error upon opening the workbook.

So the question is this. How can I protect a sheet on a shared workbook and
allow the groups to be expandable/collapsible?

Thanks is advanced,
Don

 

[Dave Peterson]

You can't change worksheet protection in a shared workbook.

You'll have to make a choice. Either share or protect.

I think Debra Dalgleish suggested making a copy of the worksheet, leaving it
unprotected and use that.

 

[Don Muller]

I'm not trying to change the worksheet protection in a shared workbook. I
setup all of the protection before I share it. It works fine before I share
but after I get errors on the code and the sheet is unprotected. So
everything works fine until I share.

 

[Dave Peterson]

Ah...

The way you get outlining/autofilter on protected sheets to work is to give tell
excel to allow it on protected sheets. The bad news is you can only tell excel
via a macro. And the way you tell it in that macro is to protect the sheet with
a special flag set.

Since you have to apply protection, it'll fail when the workbook is shared.

If you decide to remove sharing, you can use this to allow outlining:

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