protecting and sharing a workbook containing macros

[shann]

Hello all. I am trying to share a worbook that contains macros. One sheet in this book, "Stats", is protected upon opening with the code:

Private Sub Workbook_open()
Dim sh As Worksheet
Set sh = Sheets("Stats")
sh.EnableAutoFilter = True
sh.Protect contents:=True, userInterfaceOnly:=True, Password:="rats"
End Sub

This was to allow the macros to run on this protected sheet. The problem is when i set this workbook to Shared, opening it causes the error:

Run-time error '1004': Method 'Protect' of object '_Worksheet' failed

Any ideas on how i can share this successfully?

[gocush]

Have you tried manually protecting the sheet and leaving it protected rather than protecting it everytime it opens?

[shann]

I believe the problem in manually protecting the sheet is that the macro's will not run. One way would have been to have a line in the macro unprotect the sheet, then run, then protect the sheet, however i was told the above code was a simple way around having to do this on all macros.

[gocush]

Yes, I see.

I usually go thru the step of unprotecting / reprotecting in my macros, though I have seen referecne to this UserInterfaceOnly

Just a guess:

Do you also unprotect the sheets as you close?
If not then your open event is trying to protect a sheets that are already protected. could that be causing the error?

[gocush]

Is it possible that you should use either Contents:=True

or

UserInterfaceOnly:=True

but not both?

[shann]

Thanks Paul, good suggestion. Unfortunately toggling either of these fields producews undesired outcomes. I may just have to approach it in a different way. I appreciate your time.

Shannon