Nokia C3: how to enable Autofilter and Sorting in Protected Excel Sheet

how to enable Autofilter and Sorting in Protected Excel Sheet
Is it possible to "Allow" Sorting and use of Autofilter with respect to Locked cells when the worksheet is Protected? Even though I have checked both 'Sort' and 'Use Autofilter' under the "Allow all users of this worksheet to:" options list, when enabling protection, I am not able to perform either operation when the target cells are Locked.

If I set the cells to be sorted or filtered to be Unlocked then it works just fine when the worksheet protection is enabled.

I have "read" that by selecting the appropriate "Allow users to ...." option the desired functionality should work with respect to target cells that are set to the Locked status - what do I need to do differently?

Any guidance would be greatly appreciated.
Reply With Quote
#2
Old 04-01-2012
GaganjyotTechie GaganjyotTechie is offline
Member

Join Date: May 2011
Posts: 433
Re: how to enable Autofilter and Sorting in Protected Excel Sheet
Even though you select those options there are severe limitations and conditions.
Autofiltering will work only if Autofilter is enabled prior to protecting the sheetSorting will work sort only a block of contiguous unlocked cells.

The only way around all the restrictions is have a macro which Unprotects the sheet, does the sort or filter then Reprotects.
Reply With Quote
#3
Old 04-01-2012
DanielaA DanielaA is offline
Member

Join Date: Jun 2011
Posts: 754
Re: how to enable Autofilter and Sorting in Protected Excel Sheet
I usually use a _SheetActivate event to reset protection and specify UserInterfaceOnly:=True in the args (because this doesn't persist between sessions). This usually serves using VBA macros to do sorting, outlining, or toggling AutoFilter to specific ranges. I may be wrong but my understanding of how the sheet protection rules work is they are only applied to the UI, and must be managed by VBA in one way (protect/unprotect) or the other. I prefer the other because I don't need to code specially for protected sheets otherwise. Sheet protection settings are stored in local defined names so I don't have to test if a sheet needs protection reset when activated because that event fires on every sheet regardless if it's to be protected or not based on the value stored in ActiveSheet.Names("uiProtect"). Probably a bit more complex than what's needed here but it's a standard I use for all projects since most all of my projects are multi-sheet/multi-file apps.
Reply With Quote