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

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.

AccessHelp wrote:

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