resetting the autofilter for users of a workbook
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 wrote:
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
"Tom Ogilvy" wrote:
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.
--
Regards,
Tom Ogilvy
"Valeria" wrote in message
...
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").Sele ct
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
--
Dave Peterson
|