Expanding/Collapsing outlines when protected

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Blue Max - 17 Jan 2008 17:34 GMT

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?

Reply to this Message

Dave Peterson - 17 Jan 2008 18:11 GMT

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

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

Signature

Dave Peterson

Reply to this Message

Blue Max - 22 Jan 2008 18:50 GMT

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

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

>I don't think you'll find an option to use for enabling outlining. But you
>can
[quoted text clipped - 30 lines]
>> to
>> other unexpected edits?

Reply to this Message

Dave Peterson - 22 Jan 2008 20:02 GMT

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.

> 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
[quoted text clipped - 43 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

Blue Max - 22 Jan 2008 23:28 GMT

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

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

> This is a very common question--either dealing with using outlining or
> using
[quoted text clipped - 60 lines]
>> >
>> > Dave Peterson

Reply to this Message

Dave Peterson - 22 Jan 2008 23:41 GMT

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
[quoted text clipped - 75 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message