Filtering a protected WS in a shared workbook - Excel 2000

[Guest]

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]

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.

 

[Guest]

I am creating this shared workbook with multiple tabs for different groups to
fill in their data. The master worksheet contains links to other worksheets
and I'd like them to have the ability to filter but not edit (they must go to
their own worksheet to edit). Given this scenario, is there a way to protect
the master worksheet and allow filtering before sharing the workbook?

Thanks,
Phuong

 

[Dave Peterson]

Not that I know.

I am creating this shared workbook with multiple tabs for different groups to
fill in their data. The master worksheet contains links to other worksheets
and I'd like them to have the ability to filter but not edit (they must go to
their own worksheet to edit). Given this scenario, is there a way to protect
the master worksheet and allow filtering before sharing the workbook?

Thanks,
Phuong