Using Groups on Protected Sheets

[Rich K]

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 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]

Thank you. This seems to solve it.

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

 

[John M]

Rich, I'm having the same exact issue you were having with wanting to
protect a worksheet, but also give users the option to expand a collapse
groups. I tried to enter the code Dave Peterson recommended, but it doesn't
seem to work. Did you enter it in exaclty as he has written it? Thanks

 

[Dave Peterson]

Change the sheet name to match your situation--and the password, too.

And put that code into a General module.

And then close/save the workbook and reopen it -- make sure macros are enabled.

 

[Chuck D]

Hi Dave,

I too tried to key in this macro (assuming it's supposed to be in VBS?) but
it does not work when I re-launch the spreadsheet. Any help is appreciated.

Thanks, Chuck dressler

 

[Dave Peterson]

It's VBA, not VBS.

And did you put that code in a general module?
Did you change the name of the sheet to match what you need?
Did you enable macros when the workbook opened?

Hi Dave,

I too tried to key in this macro (assuming it's supposed to be in VBS?) but
it does not work when I re-launch the spreadsheet. Any help is appreciated.

Thanks, Chuck dressler

 

[Chuck D]

Sorry, VBA.

Yes, but it shows "general" and "declarations" and not "general" and "auto
open"

Yes, I changed the name of the sheet from "sheet1" to "tab 3"

Yes, macros were enabled when I opened the file.

Thanks, Chuck

 

[Dave Peterson]

This code goes into a plain old module--not behind a worksheet, not behind
ThisWorkbook, not behind a class module.

Open the workbook
hit alt-f11 to get to the VBE
then hit ctrl-r (to see the project explorer)
you can rightclick on your workbook's project and Insert|Module
Put the code in that new code window
(and remove it from whereever it was).

Sorry, VBA.

Yes, but it shows "general" and "declarations" and not "general" and "auto
open"

Yes, I changed the name of the sheet from "sheet1" to "tab 3"

Yes, macros were enabled when I opened the file.

Thanks, Chuck

 

[Chuck D]

Thanks Dave! That did it.

This code goes into a plain old module--not behind a worksheet, not behind
ThisWorkbook, not behind a class module.

Open the workbook
hit alt-f11 to get to the VBE
then hit ctrl-r (to see the project explorer)
you can rightclick on your workbook's project and Insert|Module
Put the code in that new code window
(and remove it from whereever it was).

 

[Mitch Powell]

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]

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

 

[Mitch Powell]

Your right. I spoke to quickly.

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.