I think you've got to make a choice.

If the workbook is shared, then you can't change the protection of any
worksheet.

You can't even change it to allow the code to do the changes
(Userinterfaceonly:=true).

So you can unshare it and leave the worksheet protection.

or...

You can unprotect the worksheet and leave the workbook shared.



Phuong Nguyen wrote:

Hi,

I am having problem with filtering a protected worksheet in a shared
workbook. I used the macro below which was provided on this discussion
group. When I share the workbook and open the file again, I get the
following error:

Run-time error '1004':
Protect method of Worksheet class failed

Your help is greatly appreciated!
Phuong

Private Sub Workbook_Open()
'check for filter, turn on if none exists
With Worksheets("Data")
If Not .AutoFilterMode Then
.Range("A1").AutoFilter
End If
.EnableAutoFilter = True
.Protect Password:="password", _
Contents:=True, UserInterfaceOnly:=True
End With
End Sub

--

Dave Peterson