Protect sheet but still allow use of grouping

[Terry K]

Hello all,
I have created a spreadsheet and used the grouping feature within it. I
would like to protect my formulas so that they cannot be edited and
still allow people to expand and contract the different groups. As soon
as I protect the sheet it seems as though I can no longer expand and
contact these areas. Is there any way to allow this expansion and
contraction while still protecting my formulas?
Thanks Terry

 

[Guest]

The short answer is no you can not expand / contract groups on a protected
sheet. The only thing I can recommend is to add some code that protects and
unprotects the sheet based on the contents of the currently selected cell.
Something like this...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Left(Target.Formula, 1) = "=" Or Target.Count > 1 Then
ActiveSheet.Protect
Else
ActiveSheet.Unprotect
End If
End Sub

It is not perfect but it might work for you in a pinch...

 

[Terry K]

Thank you very much Jim.
That was pretty much wat I figured. The unfortunate part is that I have
to do it with out the use of macros. I have just done away with the
grouping (Not very nice looking but at least the formulas are safe <)
The lesser of the two evils right?
Thanks Terry

 

[Guest]

If your grouping ws created using the automated subtotal feature you might be
able to switch to a pivot table instead (just a thought). The users can not
overwrite the pivot table data.