Programatically Filter a protected shared worksheet

[Neill]

How can I filter a protected shared worksheet?
1. I have allowed filtering in protection & can filter the protected
worksheet manually but if I try using code it says its protected. Code used
to filter Selection.AutoFilter Field:=3, Criteria1:="LK"
2. I can't unprotect then filter because it's a shared workbook.
3. I have used Protect UserInterfaceOnly:=True but this resets after closing
& cannot be reapplied as you cant protect a shared workbook.

Regards
Neill

 

[Dave Peterson]

You're going to have to make a choice--share the workbook or allow filtering.

Or come up with a different idea.

Debra Dalgleish once suggested something like this:

Create a new sheet
Keep it unprotected
Fill it with formulas like:

=if(sheet1!a1="","",sheet1!a1)

so all the formulas retrieve their values from the "real" sheet.

Then apply filtering to that unprotected sheet.

You may want to remind the users that changes to this sheet are not
important--it's only there to allow them to use the filter. (Maybe give all the
cells in that sheet a different fill color to make it look different.)