Group - Ungroup and Protect Sheet

[[email protected]]

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


"[email protected]" wrote:
>
> 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

[Glen Mettler]

Works like a champ! Thanks

Glen

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
> "[email protected]" wrote:
>>
>> 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
>