Group and Outline on a Protected Sheet

[AccessHelp]

Hello all,

I have an Excel sheet that I use the Group (Data|Group and Outline|Group) to
group certain rows. If I protect that sheet, I would not able to expand or
collapse the groups. I tried turn-on various items (and all items) when I
protect the sheet, and it still would not let me expand or collapse.

Is there a way I can use the Group on a protected sheet?

Thanks.

 

[ShaneDevenshire]

Hi,

The Group and Outlining commands are not available when the spreadsheet is
protected, however, you might want to consider using a pivot table since you
can protect them but still retain most of their functionality. And they have
very nice grouping features.

 

[AccessHelp]

Shane,

Thanks for the information. Unfortunately, I can not use the pivot table
for what I am doing.

Thanks.

 

[Dave Peterson]

If you already have the outline/subtotals/autofilter 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
'If .FilterMode Then
' .ShowAllData
'End If
End With
End Sub

It needs to be reset each time you open the workbook. (Earlier versions of
excel don't remember it after closing the workbook. IIRC, xl2002+ will remember
the allow autofilter setting under tools|Protection|protect sheet.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[AccessHelp]

Dave,

Thank you very much for the code. The code works perfectly. Can you help
me one more thing?

I have 10 sheets in the workbook, and I would like to use your code for 4 of
the 10 sheets. How can I change your code to work on those 4 sheets (e.g.
Sheet1, Sheet2, Sheet3 and Sheet4)? I tried to change it to "With
Worksheets("Sheet1", "Sheet2", "Sheet3", "Sheet4")".

Thanks.

 

[Dave Peterson]

One way:

Option Explicit
Sub auto_open()
dim mySheetNames as variant
dim iCtr as long
mySheetnames = array("sheet1","sheet2","sheet3","sheet4")
for ictr = lbound(mysheetnames) to ubound(mysheetnames)
With Worksheets(mysheetnames(ictr))
.Protect Password:="hi", userinterfaceonly:=True
.EnableOutlining = True
'.EnableAutoFilter = True
'If .FilterMode Then
' .ShowAllData
'End If
End With
next ictr
End Sub

(Untested, uncompiled. Watch for typos.)

 

[AccessHelp]

Dave,

Thank you very much for the code. Again, you code works perfectly.

 

[AccessHelp]

Good morning Dave,

After using your code, I noticed two things. One is when I opened the
workbook, I was not prompted to enable the macro (Security Warnings screen).
The other one is how does Excel know to execute the code/macro when the
workbook is opened. Usually, the user has to run the macro to execute the
code.

If you could, please share with me those knowledge.

Thanks. As always, you are the best!

 

[Dave Peterson]

Depending on your version of excel, there's a setting that allows you to disable
all macros, allow only signed macros to run, or get prompted to see if you want
to allow macros, or allow all macros (with no prompt).

In xl2003, it's:
tools|macro|security|Security level tab

If the user has the highest setting, they won't get prompted at all and macros
won't run. If they got a prompt and said no, then the macro won't run.

And the name of the macro was special--it's called Auto_Open. This procedure in
a general module will run each time you open the workbook (well, and allow
macros). (And a procedure named Auto_Close will run when you close the
workbook.)

There are other ways, too.

There's a workbook_Open event that goes under the ThisWorkbook module. They
essentially can do the same thing.

There are lots of other events that make running macros seem like magic.

Good morning Dave,

After using your code, I noticed two things. One is when I opened the
workbook, I was not prompted to enable the macro (Security Warnings screen).
The other one is how does Excel know to execute the code/macro when the
workbook is opened. Usually, the user has to run the macro to execute the
code.

If you could, please share with me those knowledge.

Thanks. As always, you are the best!

 

[AccessHelp]

Dave,

Thank you very much for taking the time to explain to me. Where do you
think I can find those special macro names (auto_open, etc.)?

Thanks.

 

[Dave Peterson]

The only two that I know of that are in a General module are the Auto_Open and
Auto_Close.

But you can find more info about those other events here.

Chip Pearson has some instructions on events:
http://www.cpearson.com/excel/Events.aspx

David McRitchie has some notes, too:
http://www.mvps.org/dmcritchie/excel/event.htm

And there are other application events, too:
Chip Pearson's site:
http://www.cpearson.com/excel/AppEvent.aspx

Dave,

Thank you very much for taking the time to explain to me. Where do you
think I can find those special macro names (auto_open, etc.)?

Thanks.

 

[AccessHelp]

Dave,

Thanks again for the information. Have a great weekend!

 

[mlge69]

Dave, I need more help. I've used the code you documented for my workbook which has multiple sheets (I edited the array to handle sheets 4 through 15). I've checked Macro security which is setup as Medium, everything else seems fine, yet when I open the workbook, it does not work. If I reduce the array to one sheet, then it does work. Any ideas what I might have wrong?

Thanks,
Mike

 

[Me]

Dave,
I copied what you have below and inserted it into my sheet. I can't get it
to work automatically. If I go into macros and tell it to run, then it does
great. What am I missing? I am using Excel 2007

Thanks

 

[Dave Peterson]

If you copied the code into the worksheet module, then that isn't the correct
location.

Move the code into a General module (Insert|Module type).

Dave,
I copied what you have below and inserted it into my sheet. I can't get it
to work automatically. If I go into macros and tell it to run, then it does
great. What am I missing? I am using Excel 2007

Thanks