Expanding/Collapsing outlines when protected

[Blue Max]

When protecting a worksheet, what options must we select as exceptions in
order for the user to be able to expand or collapse outlines when the
worksheet is protected? If there is such an option does it open the door to
other unexpected edits?

 

[Dave Peterson]

I don't think you'll find an option to use for enabling outlining. But you can
use code:

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

 

[Blue Max]

Thank you Dave. Before I try the macro I have two questions. FIRST, are
several lines of the macro you sent 'commented-out' with an apostrophe for a
reason, or was this inadvertant? SECOND, how do these lines, relating to
filtering, relate to this macro?

Thanks,
Richard

*************

 

[Dave Peterson]

This is a very common question--either dealing with using outlining or using
autofilter. I copy and paste an existing response. Sometimes I delete the
stuff that doesn't apply to the original post. Sometimes I comment it out.

If you don't need the stuff for autofiltering, you can ignore or delete the
commented code.

 

[Blue Max]

Understood! Thanks. In conclusion, it appears we can turn on OUTLINING for
a protected document without removing the protection, but cannot do so as an
option when protecting the document? Sure seems like it would be a simple
item for Microsoft to add as an exception when protecting the document,
especially since it can already be done with code. We certainly hope that
they will consider this option in the future.

Thanks again,
Richard

*******************

 

[Dave Peterson]

Actually, you're applying the worksheet protection in code. So you could look
at it as though you are removing the protection (just momentarily).

The reason this makes a difference is if you share the workbook. When workbooks
are shared, you can't change any worksheet's protection--including adding the
ability to use the outlining.

I don't think anyone from MS will actually read your hope--maybe by accident.

Understood! Thanks. In conclusion, it appears we can turn on OUTLINING for
a protected document without removing the protection, but cannot do so as an
option when protecting the document? Sure seems like it would be a simple
item for Microsoft to add as an exception when protecting the document,
especially since it can already be done with code. We certainly hope that
they will consider this option in the future.

Thanks again,
Richard

*******************