| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filter a protected shared worksheet
How can I filter a protected shared worksheet?
I have allowed filtering in protection & can filter the protected worksheet manually but if I try using vba it says its protected. I can't unprotect then filter because it's a shared workbook. Command used to try to filter is: Selection.AutoFilter Field:=3, Criteria1:="LK", VisibleDropDown:=False -- Regards Neill |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Filter a protected shared worksheet
hi Neill,
Have you tried the "user interface only" approach of protecting sheets? (I haven't tested this on a shared file) 'copy this into the "thisworkbook" code window Option Explicit Private Sub Workbook_Open() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Protect UserInterfaceOnly:=True Next ws End Sub To see the other arguments/parameters that can be set when protecting sheets, select "protect" & press [F1] in the VB Editor. My understanding is that the "user interface" option needs to be reset every time the file is opened which is I've put it in the thisworkbook code section. hth Rob Rob Brockett NZ Always learning & the best way to learn is to experience... "Neill" wrote: How can I filter a protected shared worksheet? I have allowed filtering in protection & can filter the protected worksheet manually but if I try using vba it says its protected. I can't unprotect then filter because it's a shared workbook. Command used to try to filter is: Selection.AutoFilter Field:=3, Criteria1:="LK", VisibleDropDown:=False -- Regards Neill |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Filter a protected shared worksheet
Excellent 1st time I,ve post to a newsgroup & your answer works perfect, many
thanks. -- Neill "broro183" wrote: hi Neill, Have you tried the "user interface only" approach of protecting sheets? (I haven't tested this on a shared file) 'copy this into the "thisworkbook" code window Option Explicit Private Sub Workbook_Open() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Protect UserInterfaceOnly:=True Next ws End Sub To see the other arguments/parameters that can be set when protecting sheets, select "protect" & press [F1] in the VB Editor. My understanding is that the "user interface" option needs to be reset every time the file is opened which is I've put it in the thisworkbook code section. hth Rob Rob Brockett NZ Always learning & the best way to learn is to experience... "Neill" wrote: How can I filter a protected shared worksheet? I have allowed filtering in protection & can filter the protected worksheet manually but if I try using vba it says its protected. I can't unprotect then filter because it's a shared workbook. Command used to try to filter is: Selection.AutoFilter Field:=3, Criteria1:="LK", VisibleDropDown:=False -- Regards Neill |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Filter a protected shared worksheet
Just to add to the previous comment. You should be enabling autofilter
before protecting/sharing the sheet. 1. Enable autofilter by going to Data|Autofilter or Selection.Autofilter in your code. 2. Protect the sheet and allow autofilter to be used: ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFiltering:=True 3. Share the workbook (can't recall how to do this in code, sorry) HTH, JP On Nov 14, 6:25 am, Neill wrote: How can I filter a protected shared worksheet? I have allowed filtering in protection & can filter the protected worksheet manually but if I try using vba it says its protected. I can't unprotect then filter because it's a shared workbook. Command used to try to filter is: Selection.AutoFilter Field:=3, Criteria1:="LK", VisibleDropDown:=False -- Regards Neill |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Filter a protected shared worksheet
This code worked for you in a shared workbook (tools|share workbook type
sharing)? Neill wrote: Excellent 1st time I,ve post to a newsgroup & your answer works perfect, many thanks. -- Neill "broro183" wrote: hi Neill, Have you tried the "user interface only" approach of protecting sheets? (I haven't tested this on a shared file) 'copy this into the "thisworkbook" code window Option Explicit Private Sub Workbook_Open() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Protect UserInterfaceOnly:=True Next ws End Sub To see the other arguments/parameters that can be set when protecting sheets, select "protect" & press [F1] in the VB Editor. My understanding is that the "user interface" option needs to be reset every time the file is opened which is I've put it in the thisworkbook code section. hth Rob Rob Brockett NZ Always learning & the best way to learn is to experience... "Neill" wrote: How can I filter a protected shared worksheet? I have allowed filtering in protection & can filter the protected worksheet manually but if I try using vba it says its protected. I can't unprotect then filter because it's a shared workbook. Command used to try to filter is: Selection.AutoFilter Field:=3, Criteria1:="LK", VisibleDropDown:=False -- Regards Neill -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Filter a protected shared worksheet
Good point JP.
Neil, Thanks for the feedback, pleased we could help :-) __________________ Rob Brockett NZ Always learning & the best way to learn is to experience... "JP" wrote: Just to add to the previous comment. You should be enabling autofilter before protecting/sharing the sheet. 1. Enable autofilter by going to Data|Autofilter or Selection.Autofilter in your code. 2. Protect the sheet and allow autofilter to be used: ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFiltering:=True 3. Share the workbook (can't recall how to do this in code, sorry) HTH, JP On Nov 14, 6:25 am, Neill wrote: How can I filter a protected shared worksheet? I have allowed filtering in protection & can filter the protected worksheet manually but if I try using vba it says its protected. I can't unprotect then filter because it's a shared workbook. Command used to try to filter is: Selection.AutoFilter Field:=3, Criteria1:="LK", VisibleDropDown:=False -- Regards Neill |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Filter a protected shared worksheet
Spoke to soon
Yes I protected using ws.Protect UserInterfaceOnly:=True before I shared the workbook & all worked fine, I shared it saved it & all ok. However after closing & re-opening it stops working? Regards Neill "broro183" wrote: Good point JP. Neil, Thanks for the feedback, pleased we could help :-) __________________ Rob Brockett NZ Always learning & the best way to learn is to experience... "JP" wrote: Just to add to the previous comment. You should be enabling autofilter before protecting/sharing the sheet. 1. Enable autofilter by going to Data|Autofilter or Selection.Autofilter in your code. 2. Protect the sheet and allow autofilter to be used: ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFiltering:=True 3. Share the workbook (can't recall how to do this in code, sorry) HTH, JP On Nov 14, 6:25 am, Neill wrote: How can I filter a protected shared worksheet? I have allowed filtering in protection & can filter the protected worksheet manually but if I try using vba it says its protected. I can't unprotect then filter because it's a shared workbook. Command used to try to filter is: Selection.AutoFilter Field:=3, Criteria1:="LK", VisibleDropDown:=False -- Regards Neill |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Filter a protected shared worksheet
The main problem with all this is you can't protect or unprotect a shared
workbook neither can you filter it using code even though you have allowed it in filter settings & it filters manually but not with code. Selection.AutoFilter Field:=3, Criteria1:="LK", VisibleDropDown:=False -- Regards Neill "Neill" wrote: Spoke to soon Yes I protected using ws.Protect UserInterfaceOnly:=True before I shared the workbook & all worked fine, I shared it saved it & all ok. However after closing & re-opening it stops working? Regards Neill "broro183" wrote: Good point JP. Neil, Thanks for the feedback, pleased we could help :-) __________________ Rob Brockett NZ Always learning & the best way to learn is to experience... "JP" wrote: Just to add to the previous comment. You should be enabling autofilter before protecting/sharing the sheet. 1. Enable autofilter by going to Data|Autofilter or Selection.Autofilter in your code. 2. Protect the sheet and allow autofilter to be used: ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFiltering:=True 3. Share the workbook (can't recall how to do this in code, sorry) HTH, JP On Nov 14, 6:25 am, Neill wrote: How can I filter a protected shared worksheet? I have allowed filtering in protection & can filter the protected worksheet manually but if I try using vba it says its protected. I can't unprotect then filter because it's a shared workbook. Command used to try to filter is: Selection.AutoFilter Field:=3, Criteria1:="LK", VisibleDropDown:=False -- Regards Neill |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Filter a protected shared worksheet
You just have to follow the steps in the correct order, refer to my
earlier post. HTH, JP On Nov 15, 4:51 am, Neill wrote: The main problem with all this is you can't protect or unprotect a shared workbook neither can you filter it using code even though you have allowed it in filter settings & it filters manually but not with code. Selection.AutoFilter Field:=3, Criteria1:="LK", VisibleDropDown:=False -- Regards Neill "Neill" wrote: Spoke to soon Yes I protected using ws.Protect UserInterfaceOnly:=True before I shared the workbook & all worked fine, I shared it saved it & all ok. However after closing & re-opening it stops working? Regards Neill "broro183" wrote: Good point JP. Neil, Thanks for the feedback, pleased we could help :-) __________________ Rob Brockett NZ Always learning & the best way to learn is to experience... "JP" wrote: Just to add to the previous comment. You should be enabling autofilter before protecting/sharing the sheet. 1. Enable autofilter by going to Data|Autofilter or Selection.Autofilter in your code. 2. Protect the sheet and allow autofilter to be used: ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFiltering:=True 3. Share the workbook (can't recall how to do this in code, sorry) HTH, JP On Nov 14, 6:25 am, Neill wrote: How can I filter a protected shared worksheet? I have allowed filtering in protection & can filter the protected worksheet manually but if I try using vba it says its protected. I can't unprotect then filter because it's a shared workbook. Command used to try to filter is: Selection.AutoFilter Field:=3, Criteria1:="LK", VisibleDropDown:=False -- Regards Neill- Hide quoted text - - Show quoted text - |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Filter a protected shared worksheet
But you can't change the protection status of any worksheet in a shared
workbook. And since that userinterfaceonly setting is forgotten after you close the file, the code needs to be rerun each time the workbook opens. And if the workbook is shared, the code will fail. JP wrote: You just have to follow the steps in the correct order, refer to my earlier post. HTH, JP On Nov 15, 4:51 am, Neill wrote: The main problem with all this is you can't protect or unprotect a shared workbook neither can you filter it using code even though you have allowed it in filter settings & it filters manually but not with code. Selection.AutoFilter Field:=3, Criteria1:="LK", VisibleDropDown:=False -- Regards Neill "Neill" wrote: Spoke to soon Yes I protected using ws.Protect UserInterfaceOnly:=True before I shared the workbook & all worked fine, I shared it saved it & all ok. However after closing & re-opening it stops working? Regards Neill "broro183" wrote: Good point JP. Neil, Thanks for the feedback, pleased we could help :-) __________________ Rob Brockett NZ Always learning & the best way to learn is to experience... "JP" wrote: Just to add to the previous comment. You should be enabling autofilter before protecting/sharing the sheet. 1. Enable autofilter by going to Data|Autofilter or Selection.Autofilter in your code. 2. Protect the sheet and allow autofilter to be used: ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFiltering:=True 3. Share the workbook (can't recall how to do this in code, sorry) HTH, JP On Nov 14, 6:25 am, Neill wrote: How can I filter a protected shared worksheet? I have allowed filtering in protection & can filter the protected worksheet manually but if I try using vba it says its protected. I can't unprotect then filter because it's a shared workbook. Command used to try to filter is: Selection.AutoFilter Field:=3, Criteria1:="LK", VisibleDropDown:=False -- Regards Neill- Hide quoted text - - Show quoted text - -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Filter a protected shared worksheet
Ps. If this worked for you, what version of excel are you using?
Maybe this behavior changed?? Dave Peterson wrote: But you can't change the protection status of any worksheet in a shared workbook. And since that userinterfaceonly setting is forgotten after you close the file, the code needs to be rerun each time the workbook opens. And if the workbook is shared, the code will fail. JP wrote: You just have to follow the steps in the correct order, refer to my earlier post. HTH, JP On Nov 15, 4:51 am, Neill wrote: The main problem with all this is you can't protect or unprotect a shared workbook neither can you filter it using code even though you have allowed it in filter settings & it filters manually but not with code. Selection.AutoFilter Field:=3, Criteria1:="LK", VisibleDropDown:=False -- Regards Neill "Neill" wrote: Spoke to soon Yes I protected using ws.Protect UserInterfaceOnly:=True before I shared the workbook & all worked fine, I shared it saved it & all ok. However after closing & re-opening it stops working? Regards Neill "broro183" wrote: Good point JP. Neil, Thanks for the feedback, pleased we could help :-) __________________ Rob Brockett NZ Always learning & the best way to learn is to experience... "JP" wrote: Just to add to the previous comment. You should be enabling autofilter before protecting/sharing the sheet. 1. Enable autofilter by going to Data|Autofilter or Selection.Autofilter in your code. 2. Protect the sheet and allow autofilter to be used: ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFiltering:=True 3. Share the workbook (can't recall how to do this in code, sorry) HTH, JP On Nov 14, 6:25 am, Neill wrote: How can I filter a protected shared worksheet? I have allowed filtering in protection & can filter the protected worksheet manually but if I try using vba it says its protected. I can't unprotect then filter because it's a shared workbook. Command used to try to filter is: Selection.AutoFilter Field:=3, Criteria1:="LK", VisibleDropDown:=False -- Regards Neill- Hide quoted text - - Show quoted text - -- Dave Peterson -- Dave Peterson |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Filter a protected shared worksheet
Dave can you explain further? I tested my method and it worked. As
long as you apply the autofilter first, then allow autofilter to be used when protecting the workbook, it should allow you to use the filter dropdowns after you share the workbook. Maybe it works because I am doing it by hand instead of programmatically? Thx, JP On Nov 15, 7:37 am, Dave Peterson wrote: But you can't change the protection status of any worksheet in a shared workbook. And since that userinterfaceonly setting is forgotten after you close the file, the code needs to be rerun each time the workbook opens. And if the workbook is shared, the code will fail. JP wrote: You just have to follow the steps in the correct order, refer to my earlier post. |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Filter a protected shared worksheet
I am using 2003 just to confirm the 3 points
1. You can not protect or unprotect a shared workbook 2. I can filter manually but not with VBA using "Selection.AutoFilter Field:=3, Criteria1:="LK", VisibleDropDown:=False" 3. ws.Protect UserInterfaceOnly:=True disappears after closing & can not be reapplied as workbook is shared. Regards Neill "JP" wrote: Dave can you explain further? I tested my method and it worked. As long as you apply the autofilter first, then allow autofilter to be used when protecting the workbook, it should allow you to use the filter dropdowns after you share the workbook. Maybe it works because I am doing it by hand instead of programmatically? Thx, JP On Nov 15, 7:37 am, Dave Peterson wrote: But you can't change the protection status of any worksheet in a shared workbook. And since that userinterfaceonly setting is forgotten after you close the file, the code needs to be rerun each time the workbook opens. And if the workbook is shared, the code will fail. JP wrote: You just have to follow the steps in the correct order, refer to my earlier post. |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Filter a protected shared worksheet
Do all your work (including sharing the workbook).
Save that workbook Close that workbook Reopen that workbook (it still should be shared) Try filtering the data. Does it work? What version of excel are you using? JP wrote: Dave can you explain further? I tested my method and it worked. As long as you apply the autofilter first, then allow autofilter to be used when protecting the workbook, it should allow you to use the filter dropdowns after you share the workbook. Maybe it works because I am doing it by hand instead of programmatically? Thx, JP On Nov 15, 7:37 am, Dave Peterson wrote: But you can't change the protection status of any worksheet in a shared workbook. And since that userinterfaceonly setting is forgotten after you close the file, the code needs to be rerun each time the workbook opens. And if the workbook is shared, the code will fail. JP wrote: You just have to follow the steps in the correct order, refer to my earlier post. -- Dave Peterson |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Filter a protected shared worksheet
I am using Excel 2003 SP2
I can filter manually, but if I try using VB it says its protected. Regards John "Dave Peterson" wrote: Do all your work (including sharing the workbook). Save that workbook Close that workbook Reopen that workbook (it still should be shared) Try filtering the data. Does it work? What version of excel are you using? JP wrote: Dave can you explain further? I tested my method and it worked. As long as you apply the autofilter first, then allow autofilter to be used when protecting the workbook, it should allow you to use the filter dropdowns after you share the workbook. Maybe it works because I am doing it by hand instead of programmatically? Thx, JP On Nov 15, 7:37 am, Dave Peterson wrote: But you can't change the protection status of any worksheet in a shared workbook. And since that userinterfaceonly setting is forgotten after you close the file, the code needs to be rerun each time the workbook opens. And if the workbook is shared, the code will fail. JP wrote: You just have to follow the steps in the correct order, refer to my earlier post. -- Dave Peterson |
|
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Filter a protected shared worksheet
Yep. That was my point.
Neill wrote: I am using Excel 2003 SP2 I can filter manually, but if I try using VB it says its protected. Regards John "Dave Peterson" wrote: Do all your work (including sharing the workbook). Save that workbook Close that workbook Reopen that workbook (it still should be shared) Try filtering the data. Does it work? What version of excel are you using? JP wrote: Dave can you explain further? I tested my method and it worked. As long as you apply the autofilter first, then allow autofilter to be used when protecting the workbook, it should allow you to use the filter dropdowns after you share the workbook. Maybe it works because I am doing it by hand instead of programmatically? Thx, JP On Nov 15, 7:37 am, Dave Peterson wrote: But you can't change the protection status of any worksheet in a shared workbook. And since that userinterfaceonly setting is forgotten after you close the file, the code needs to be rerun each time the workbook opens. And if the workbook is shared, the code will fail. JP wrote: You just have to follow the steps in the correct order, refer to my earlier post. -- Dave Peterson -- Dave Peterson |
|
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Filter a protected shared worksheet
Sorry don't understand your reply.
Regards Neill "Dave Peterson" wrote: Yep. That was my point. Neill wrote: I am using Excel 2003 SP2 I can filter manually, but if I try using VB it says its protected. Regards John "Dave Peterson" wrote: Do all your work (including sharing the workbook). Save that workbook Close that workbook Reopen that workbook (it still should be shared) Try filtering the data. Does it work? What version of excel are you using? JP wrote: Dave can you explain further? I tested my method and it worked. As long as you apply the autofilter first, then allow autofilter to be used when protecting the workbook, it should allow you to use the filter dropdowns after you share the workbook. Maybe it works because I am doing it by hand instead of programmatically? Thx, JP On Nov 15, 7:37 am, Dave Peterson wrote: But you can't change the protection status of any worksheet in a shared workbook. And since that userinterfaceonly setting is forgotten after you close the file, the code needs to be rerun each time the workbook opens. And if the workbook is shared, the code will fail. JP wrote: You just have to follow the steps in the correct order, refer to my earlier post. -- Dave Peterson -- Dave Peterson |
|
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Filter a protected shared worksheet
Sorry yes you were replying to somebody else's post, I did start this very
question saying I can do it manually but not programatically. Maybe I should start a fresh post? Regards Neill "Neill" wrote: Sorry don't understand your reply. Regards Neill "Dave Peterson" wrote: Yep. That was my point. Neill wrote: I am using Excel 2003 SP2 I can filter manually, but if I try using VB it says its protected. Regards John "Dave Peterson" wrote: Do all your work (including sharing the workbook). Save that workbook Close that workbook Reopen that workbook (it still should be shared) Try filtering the data. Does it work? What version of excel are you using? JP wrote: Dave can you explain further? I tested my method and it worked. As long as you apply the autofilter first, then allow autofilter to be used when protecting the workbook, it should allow you to use the filter dropdowns after you share the workbook. Maybe it works because I am doing it by hand instead of programmatically? Thx, JP On Nov 15, 7:37 am, Dave Peterson wrote: But you can't change the protection status of any worksheet in a shared workbook. And since that userinterfaceonly setting is forgotten after you close the file, the code needs to be rerun each time the workbook opens. And if the workbook is shared, the code will fail. JP wrote: You just have to follow the steps in the correct order, refer to my earlier post. -- Dave Peterson -- Dave Peterson |
|
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Filter a protected shared worksheet
I don't think it will help.
Neill wrote: Sorry yes you were replying to somebody else's post, I did start this very question saying I can do it manually but not programatically. Maybe I should start a fresh post? Regards Neill "Neill" wrote: Sorry don't understand your reply. Regards Neill "Dave Peterson" wrote: Yep. That was my point. Neill wrote: I am using Excel 2003 SP2 I can filter manually, but if I try using VB it says its protected. Regards John "Dave Peterson" wrote: Do all your work (including sharing the workbook). Save that workbook Close that workbook Reopen that workbook (it still should be shared) Try filtering the data. Does it work? What version of excel are you using? JP wrote: Dave can you explain further? I tested my method and it worked. As long as you apply the autofilter first, then allow autofilter to be used when protecting the workbook, it should allow you to use the filter dropdowns after you share the workbook. Maybe it works because I am doing it by hand instead of programmatically? Thx, JP On Nov 15, 7:37 am, Dave Peterson wrote: But you can't change the protection status of any worksheet in a shared workbook. And since that userinterfaceonly setting is forgotten after you close the file, the code needs to be rerun each time the workbook opens. And if the workbook is shared, the code will fail. JP wrote: You just have to follow the steps in the correct order, refer to my earlier post. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Spell Check in Protected Worksheet & Shared Workbook | Excel Discussion (Misc queries) | |||
| Spell Check in Protected Worksheet & Shared Workbook continued | Excel Discussion (Misc queries) | |||
| Turning Filters ON in a Protected Shared Worksheet | Excel Programming | |||
| Filter on a Protected Worksheet | Excel Programming | |||
| shared workbooks - protected worksheet - insert rows | Excel Programming | |||
Linear Mode
