Filter a protected shared worksheet

N

Neill

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
 
B

broro183

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...
 
N

Neill

Excellent 1st time I,ve post to a newsgroup & your answer works perfect, many
thanks.
--
Neill


broro183 said:
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 said:
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
Click to expand...
Click to expand...
 
J

JP

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
 
D

Dave Peterson

This code worked for you in a shared workbook (tools|share workbook type
sharing)?


Excellent 1st time I,ve post to a newsgroup & your answer works perfect, many
thanks.
--
Neill

broro183 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
B

broro183

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 said:
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
Click to expand...
 
N

Neill

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 said:
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...
Click to expand...
 
N

Neill

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
 
J

JP

You just have to follow the steps in the correct order, refer to my
earlier post.

HTH,
JP
 
D

Dave Peterson

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.
 
D

Dave Peterson

Ps. If this worked for you, what version of excel are you using?

Maybe this behavior changed??
 
J

JP

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
 
N

Neill

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
 
D

Dave Peterson

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?
 
N

Neill

I am using Excel 2003 SP2

I can filter manually, but if I try using VB it says its protected.

Regards
John
 
N

Neill

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 said:
Sorry don't understand your reply.

Regards
Neill
Click to expand...
 
D

Dave Peterson

I don't think it will help.
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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Programatically filter a protected shared workbook 4
Programatically Filter a protected shared worksheet 1
Programatically Filter a protected shared worksheet 0
Shared Workbooks & Code 1
How to Activate Filter in Protected Sheet 2
Custom filter 26
Pivot Table on protected worksheet - 10
Filter, Delete and Error handling - need help 4

Top