| 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
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 |
|
#5
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 |
|
#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 - |
| 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 | |||
Hybrid Mode
