Oddly enough, the "EnableOutlining" argument does not show up in the list of
arguments for this method in the object browser or help file...

"Dave Peterson" wrote:

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

Rich K wrote:

In order make the UI a little cleaner on a spreadsheet with a lot of data, I
have added grouped columns and rows for the user to easily collapse the data
and re-expand. This sheet is also needs to be protected from data entry
except in the few cells that are unlocked.

If I turn on the sheet protection and click on a group to expand or
collapse, I get the error "You cannot us this command on a protected
sheet......" Does anyone know how I may be able to trap this event in VBA
and then write some code to determine the type of command that was tried?
If I could determine this, then I could selectively unprotect the sheet and
group or ungroup and then protect the sheet and return control to the user.

Thanks.

--

Dave Peterson

If I look at Worksheet in VBA's help, I can scroll down the list of properties
and see .EnableOutlining.

If I do:

dim wks as worksheet
set wks = worksheets("sheet1")
with wks
'and then type the dot

Your right. I spoke to quickly.

"Dave Peterson" wrote:

If I look at Worksheet in VBA's help, I can scroll down the list of properties
and see .EnableOutlining.

If I do:

dim wks as worksheet
set wks = worksheets("sheet1")
with wks
'and then type the dot
.
'I see a list of properties/methods and .enableoutlining shows up.




Mitch Powell wrote:

Oddly enough, the "EnableOutlining" argument does not show up in the list of
arguments for this method in the object browser or help file...

"Dave Peterson" wrote:

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

Rich K wrote:

In order make the UI a little cleaner on a spreadsheet with a lot of data, I
have added grouped columns and rows for the user to easily collapse the data
and re-expand. This sheet is also needs to be protected from data entry
except in the few cells that are unlocked.

If I turn on the sheet protection and click on a group to expand or
collapse, I get the error "You cannot us this command on a protected
sheet......" Does anyone know how I may be able to trap this event in VBA
and then write some code to determine the type of command that was tried?
If I could determine this, then I could selectively unprotect the sheet and
group or ungroup and then protect the sheet and return control to the user.

Thanks.

--

Dave Peterson


--

Dave Peterson