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).



Me wrote:

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" wrote:

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 wrote:

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" wrote:

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 wrote:

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.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson