resetting the autofilter for users of a workbook

[Valeria]

Dear experts,
I have a protected sheet with autofiltering. I would like to reset the
autofilter every time a new user opens the workbook. I have a code (see
below) that works well for a user at the time, but when another user tries to
open the workbook on the server (as read-only or notify), he/she gets an
error: "method 'Worksheets' of object '_Workbook' failed"
Could you please help me?
Many thanks!
Best regards,
Valeria

Sub auto_open()
Dim i As Integer
Worksheets("Sheet1").Activate
ActiveSheet.Unprotect Password:="my_password"
ThisWorkbook.Worksheets("Sheet1").AutoFilterMode = False
ThisWorkbook.Worksheets("Sheet1").Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Protect Password:="my_password", DrawingObjects:=True,
Contents:=True, Scenarios:=True, AllowFormattingCells:=True,
AllowFormattingColumns:=False, AllowFormattingRows:=True, AllowSorting:=True,
AllowFiltering:=True
End Sub

 

[Tom Ogilvy]

I suspect the user with the error is using a xl2000 or earlier. Those
options for the Protect method were not available in that version.

 

[Valeria]

Hi Tom,
no, he's using Excel 2002 under XP Pro... I guess the error comes somehow
from the fact that the workbook might be sometimes already open by another
user, as it's on the server ... but I can't understand why it's happening and
how to avoid it!
Best regards,
Valeria

 

[Dave Peterson]

I tried opening a workbook with your code in it in two separate instances of
excel (to simulate being open by another user).

It worked fine for me (xl2002).

I don't see anything in your code that would cause it to fail (if the users are
all using xl2002+), but this version (without the selects worked ok for me
(too)).

Sub auto_open()
With Worksheets("sheet1")
.Activate
.Unprotect Password:="my_password"
.AutoFilterMode = False
.Rows("1:1").AutoFilter
.Protect Password:="my_password", DrawingObjects:=True, _
Contents:=True, Scenarios:=True, AllowFormattingCells:=True, _
AllowFormattingColumns:=False, AllowFormattingRows:=True, _
AllowSorting:=True, AllowFiltering:=True
End With
End Sub

 

[Valeria]

Hi,
thank you. Your code works fine for me too... so I'll use it and I think
I'll add a "on error resume next" line!
Thanks,
Best regards,
Valeria