Enabling Autofilter and Sorting in a Protected Sheet

  • Thread starter Thread starter jraonline
  • Start date Start date
J

jraonline

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 greaty appreciated.

Thanks!

John
 
Even though you select those options there are severe limitations and
conditions

Autofiltering.................only if Autofilter is enabled prior to
protecting the sheet

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



Gord
 
Gord Dibben was thinking very hard :
The only way around all the restrictions is have a macro which
Unprotects the sheet, does the sort or filter then Reprotects.
Click to expand...

To add...
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.
 
Back
Top