Worksheet protection with grouped rows?

[pmw5]

Hi,
I have a report which has some grouped rows. The normal view of the report
shows these rows grouped, and therefore only shows the total of the
hidden/grouped rows.

I want to protect certain cells in the report so they cannot be modified,
however, when I protect the sheet, I can no longer ungroup the grouped rows.

I could of course ungroup all the grouped rows and then protect the sheet,
however the report would be much to big then.

Does anyone know how to protect a worksheet yet still be able to use the
group/ungroup rows buttons?

Thanks in advance.

 

[Frank Kabel]

Hi
AFAIK not possible without creating a macro which first unprotects the
sheet, groups/ungroups and protects t´he sheet again

 

[pmw5]

Frank,

Thanks for your answer. I can see logically how your solution would work,
however writing such a macro is beyond my skills! Thanks anyway for the
answer. (also what does AFAIK stand for?)

Does anyone else have any suggestions? I am amazed XL doesn't allow you to
check some option to allow the group/ungroup function to still work on
protected sheets...

 

[Norman Jones]

Hi Pmw5,

With the worksheet unprotected:

Right-Click the Excel icon at the extreme left of the Menu Bar
Select the View Code option
Paste the following code:

Private Sub Workbook_Open()
With Worksheets("Sheet1") '<<===== CHANGE SHEET NAME
.EnableOutlining = True
.Protect Password:="PASSWORD", _
Contents:=True, UserInterfaceOnly:=True '<< CHANGE PASSWORD
End With

End Sub

Change PASSWORD to your choice of password.
Change Sheet1 to your sheet name.

Click anywhere in the posted code and hit the F5 function key.

Save the workbook.

 

[Norman Jones]

Hi Pmw5,

Missed one step:

After hitting the F5 key, Press Alt-F11 to return to Excel

 

[pmw5]

Many thanks Norman! Much appreciated.

Hi Pmw5,

Missed one step:

After hitting the F5 key, Press Alt-F11 to return to Excel

 

[E Halliday]

Does anyone know how to do this with multiple worksheets? I appear to be
able to do with a single sheet - but it does not recognise the code on a
second one.

Thanks

 

[Gord Dibben]

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
With ws
.EnableOutlining = True
.Protect Password:="PASSWORD", _
Contents:=True, UserInterfaceOnly:=True
End With
Next ws
End Sub


Gord Dibben MS Excel MVP