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.

--
Regards,
Tom Ogilvy

"Valeria" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[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

"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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
>
>
>

[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 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > >
> >
> >
> >

--

Dave Peterson

[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

"Dave Peterson" wrote:

> 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > > >
> > >
> > >
> > >
>
> --
>
> Dave Peterson
>