protecting and grouping

[magell]

I have created a workbook which is requires user's inputs on rows which I have grouped.

It also contains complex formulas which shouldn't be deleted by the user. I have unlocked the input cells and protected the rest. The problem is that after the worksheet has been protected, you can't expand and collaps the grouped rows anymore. How can I achieve both the purpose of protecting certain cells with formulas and at the same time allow grouping and expanding of rows.

[Damon Ostrander]

Hi magell,

I don't believe there is any way to change the
outline level of a protected worksheet. However, you can have a macro
unprotect the sheet, change the outline level, and re-protect the
sheet in one step. Of course, since the sheet is protected you will
want to also protect the VBAproject (macros) so that the user cannot
see the password you used to protect/unprotect the sheet.

You can add your own buttons and assign them to macros that set the
outlining levels--you will probably want to do this since the outlining
buttons don't work on a protected sheet. For example, you might want to
create three buttons on your sheet with the captions "Show All", "Show
Subtotals", and "Totals Only", and assign them to the following three
macros respectively:


Sub ShowOutline1()
With Worksheets(1)
.Unprotect password:=""
.Outline.ShowLevels rowlevels:=1
.Protect password:=""
End With
End Sub

Sub ShowOutline2()
With Worksheets(1)
.Unprotect password:=""
.Outline.ShowLevels rowlevels:=2
.Protect password:=""
End With
End Sub

Sub ShowOutline3()
With Worksheets(1)
.Unprotect password:=""
.Outline.ShowLevels rowlevels:=3
.Protect password:=""
End With
End Sub