Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
resetting the autofilter for users of a workbook
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
resetting the autofilter for users of a workbook
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
resetting the autofilter for users of a workbook
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
resetting the autofilter for users of a workbook
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" 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Shared Workbook-Formulas constantly resetting to manual | Excel Discussion (Misc queries) | |||
HELP: Use the autofilter result on one workbook to filter the next list on another workbook | Excel Worksheet Functions | |||
How do i show other users that i am in a particular workbook | Excel Discussion (Misc queries) | |||
Users on a Shared workbook | Excel Programming | |||
Prevent users from using AutoFilter | Excel Programming |