Group - Ungroup and Protect Sheet

[glen.e.mettler]

I have a sheet that has rows and columns that are grouped.
I need to protect rollup portion of the grouped rows.
However, when I do that and protect the sheet, I can no longer group
and ungroup.
How can I allow the user to group and ungroup rows and columns but
protect the grouped row?
Example:
row 4 is a rollup of MY DATA with the next 10 rows as
supporting/detailed data. I have created a group so that I can
contract 9 of the rows and show only the rollup MY DATA or expand it
and show all the subordinate data. The formulas in the columns MY DATA
are protected while the cells in the supporting rows are available to
the user for change.

However, when I protect the sheet, the user can no longer group and
ungroup. How can I fix this?

Glen

 

[Dave Peterson]

If you already have the outline/subtotals applied, you can protect the worksheet
in code (auto_open/workbook_open??).

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
.Protect Password:="hi", userinterfaceonly:=True
.EnableOutlining = True
.EnableAutoFilter = True
End With
End Sub

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

 

[Glen Mettler]

Works like a champ! Thanks

Glen

If you already have the outline/subtotals applied, you can protect the
worksheet
in code (auto_open/workbook_open??).

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
.Protect Password:="hi", userinterfaceonly:=True
.EnableOutlining = True
.EnableAutoFilter = True
End With
End Sub

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