none
Auto Filtering With Sheet Protect RRS feed

  • Question

  • I am using auto filtering in Excel 2007 on a worksheet which is protected.  The protection is applied by a macro which unprotects then reprotects the worksheet and sets up a table in between.  I am using code for applying the protection that should work in theory, but the sheet just will not sort.  Everything is there, and the filter menus pop up when the arrow is clicked on.  When I try a sort, however, nothing happens.

    When I turn off the protecting, everything is fine...the filters work great.  There is one interesting thing happening that I don't understand, however.  When I reprotect the sheet, the menu comes up, and, because of the macro, the allow auto filtering check box is already checked.  Shouldn't the auto-filtering work under these circumstances?

    Here is the code line I am using to activate the autofiltering with the cell protection:

     destWS.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
            , AllowFiltering:=True

    destWS is the name of the worksheet in the macro.

    What am I missing?  Everything seems correct, but the autofilters won't work...

    Thanks for any information...

    -EDIT...just noticed that the "Numbers Filters" work.  That's better than nothing I guess, but the sorts selection on the auto filter menu doesn't function whatsoever.  The filters are really there for the sort

    Also, just tried this code, adding the bit about sorts thinking that maybe the extra is necessary for the sorts to happen from the auto filter menu:

     destWS.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
            , AllowFiltering:=True, AllowSorting:=True

    After running this code, I get an error message when attempting to sort with the auto filter:

    "The cell or chart that you are trying to sort is protected and therefore read only."
    "To modify a protected cell or chart, first remove the protection by selecting the Unprotect Sheet command (Review tab, Changes group).  You may be prompted for a password."

    I hoped I had figured it out with the AllowSorting addition to the macro line, but apparently not.  This bit does place a check mark in the Protect menu "Sorts" check box, along with the "Auto Filtering" check box...

    • Edited by AtlBo Friday, January 27, 2012 7:54 AM Add Info
    Friday, January 27, 2012 7:28 AM

Answers

  • Extract from Excel Help under Allow Sorting property

    "Sorting can only be performed on unlocked or unprotected cells in a protected worksheet."

    Therefore you need to unlock the cells to be sorted prior to protecting the worksheet.

    If AllowSorting is not enabled during protection then the cells cannot be sorted on a protected sheet even though the cells are unlocked. (The sort is disabled.)

     I realize this is confusing but that is how it is.


    Regards, OssieMac
    • Edited by OssieMac Friday, January 27, 2012 10:54 AM
    • Proposed as answer by danishaniModerator Friday, January 27, 2012 5:53 PM
    • Marked as answer by AtlBo Friday, January 27, 2012 8:07 PM
    Friday, January 27, 2012 10:53 AM

All replies

  • Extract from Excel Help under Allow Sorting property

    "Sorting can only be performed on unlocked or unprotected cells in a protected worksheet."

    Therefore you need to unlock the cells to be sorted prior to protecting the worksheet.

    If AllowSorting is not enabled during protection then the cells cannot be sorted on a protected sheet even though the cells are unlocked. (The sort is disabled.)

     I realize this is confusing but that is how it is.


    Regards, OssieMac
    • Edited by OssieMac Friday, January 27, 2012 10:54 AM
    • Proposed as answer by danishaniModerator Friday, January 27, 2012 5:53 PM
    • Marked as answer by AtlBo Friday, January 27, 2012 8:07 PM
    Friday, January 27, 2012 10:53 AM
  • Thankyou OssieMac...

    That is a little confusing to me, but I am happy to have the answer.

    I would like to know why Microsoft chose to value protection over sorting for Excel given that cell protection can be password protected.  Seems like a waste of a very good feature of the protection dialog...workbook creator/manager can choose what page/cell features are available to users with the protection on.  In my situation, I have a large number of formulas that need to be protected, but I would like to be able to access the features of the autofilter, so I guess I am out of luck.  Given the effort Microsoft obviously went to give creators/managers options with respect to cell and sheet features under protection it is definitely surprising.  Just can't think of the reason MS would have chosen to go that route with Excel

    Thanks again...

    Friday, January 27, 2012 11:37 AM
  • Hello again AltBo,

    As a suggestion, as you are already using code in your project maybe you could use code to unprotect and do the sort and protect again. Of course you will have to think about how the user will pass the sort parameters that would normally be selected in the sort dialog box but should not be impossible. (My initial thoughts would be to create a userform with combo boxes and radio buttons. You could almost emulate the sort dialog box.)


    Regards, OssieMac

     


    • Edited by OssieMac Friday, January 27, 2012 8:26 PM
    Friday, January 27, 2012 8:16 PM
  • Good idea OssieMac.  I was considering options like "can I set the code so that any time I select a sort the protection is turned off and then back on after the sort.?"

    Any idea is that is possible?  Sort of an "f sort/then unprotect" followed by "protect".  Maybe an On Click macro.  If the macro were set so that all uses of the mouse were treated normally with the exception of the sort click, I guess that would work...if that is possible.  The only need for a mouse click on the worksheet would be so that the auto filter interface can be opened and so that the user can change to another worksheet.


    • Edited by AtlBo Friday, January 27, 2012 8:48 PM grammar
    Friday, January 27, 2012 8:46 PM