Thread: Protect sheet but still allow use of grouping

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

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

Jim Thomlinson


"Terry K" wrote:

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

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

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

Jim Thomlinson


"Terry K" wrote:

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