expanding or collapsing groupings in protected worksheets

[vince @ west electric group]

Help!!! I have a protected worksheet which I share with managers. I've
included groupings which I want the managers to expand or collapse, but they
can't because the worksheet is protected. What can I do to allow collapsing
and expanding groups on a protected worksheet. 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, but that
won't help when you're filtering via code.)

======
I wasn't sure how you created the groupings--data|group or
data|filter|autofilter. You probably won't need both the .enableoutlining and
..enableautofilter. Delete/comment the one you don't want.

If you used data|filter|autofilter and want to make sure that all the data is
visible, you may want to uncomment those if/then/end if lines.

 

[vince @ west electric group]

Dave, can you give me line by line instructions on how and where to apply
your solution? Also, do I need to re-establish your solution every time the
worksheet is used in excel 2003?

Thanks

 

[Dave Peterson]

Yes, you need to have this code run each time the workbook is opened.

By naming the routing Auto_Open and putting it in a General module, excel will
run this code automatically--well, if the user allows macros to run!

Open excel
Open your workbook
Hit alt-f11 to get to the VBE where that code will be located.
Hit ctrl-r to see the project explorer (like windows explorer)
Select your project--you should see the workbook's name in parentheses.
Insert|Module

Paste that code in the code window that just opened.

Make sure that you change the sheet name and the password to what you need.

Hit alt-f11 to get back to excel.
Save your file
Close the file
Reopen the file
Answer yes to allow macros (if prompted)

Did it work?

Dave, can you give me line by line instructions on how and where to apply
your solution? Also, do I need to re-establish your solution every time the
worksheet is used in excel 2003?

Thanks

 

[Dore Atkinson]

Not sure if this solution worked for the original person that posted the questions, but it worked wonderfully for me. Thank you very much Dave.

 

[Dave Peterson]

I'm not sure which Dave posted the response that worked for you, but I'm sure
all Dave's will join me in saying:

Glad it worked for you!

<vbg>