Results 1 to 3 of 3

Thread: how to enable Autofilter and Sorting in Protected Excel Sheet

  1. #1
    Join Date
    Nov 2011
    Posts
    61

    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.

  2. #2
    Join Date
    May 2011
    Posts
    448

    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.

  3. #3
    Join Date
    Jun 2011
    Posts
    798

    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.

Similar Threads

  1. Replies: 2
    Last Post: 23-02-2012, 02:29 PM
  2. Replies: 5
    Last Post: 24-01-2012, 12:40 PM
  3. How to do Copy/Paste on protected work sheet in Microsoft Excel
    By Chini mao in forum MS Office Support
    Replies: 1
    Last Post: 14-01-2012, 06:28 PM
  4. Excel autofilter stops filtering
    By roumieh2000 in forum Windows Software
    Replies: 3
    Last Post: 15-10-2009, 03:01 PM
  5. Microsoft Excel autofilter has stopped working
    By McGrawh in forum Windows Software
    Replies: 4
    Last Post: 07-05-2009, 02:01 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,638,603,403.92936 seconds with 17 queries