Results 1 to 7 of 7

Protect Sheet & still use the sort with Autofilter

This is a discussion on Protect Sheet & still use the sort with Autofilter within the Excel Questions forums, part of the Question Forums category; I have a sheet where columns B3:E65536 are unlocked. I want to be able to password protect the sheet & ...

  1. #1
    Board Regular Phixtit's Avatar
    Join Date
    Oct 2008
    Posts
    346

    Arrow Protect Sheet & still use the sort with Autofilter

    I have a sheet where columns B3:E65536 are unlocked.
    I want to be able to password protect the sheet & still be able to use the sort feature with Autofilter.
    Can this be acheived with a Macro & if so any ideas on how I could do this?
    I am running Excel 2010 64-bit on Windows 7 Ultimate 64-bit.

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Sudbury, Ontario Canada
    Posts
    1,360

    Default Re: Protect Sheet & still use the sort with Autofilter

    Hi Phixtit:

    I had a similar problem before. When you protect the sheet you need to ADD a couple of check marks under the section

    "Allow all users of this worksheet to:"

    Check off "Sort" and "Use Auto Filter" and this may resolve your problem.

    As for the VBA Code here is what I get when I record the macro as described above. Hopefully you can implement it:

    Code:
     
    Sub ProtectWithAutoFilterAndSortCapabilities()
    '
    ' ProtectWithAutoFilterAndSortCapabilities Macro
    ' Macro recorded 27/05/2009
    '
    '
        ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
            , AllowSorting:=True, AllowFiltering:=True
    End Sub
    Good Luck. Hope this helps.
    Mark

  3. #3
    Board Regular Phixtit's Avatar
    Join Date
    Oct 2008
    Posts
    346

    Default Re: Protect Sheet & still use the sort with Autofilter

    Quote Originally Posted by Mister H View Post
    Hi Phixtit:

    I had a similar problem before. When you protect the sheet you need to ADD a couple of check marks under the section

    "Allow all users of this worksheet to:"

    Check off "Sort" and "Use Auto Filter" and this may resolve your problem.

    As for the VBA Code here is what I get when I record the macro as described above. Hopefully you can implement it:

    Code:
     
    Sub ProtectWithAutoFilterAndSortCapabilities()
    '
    ' ProtectWithAutoFilterAndSortCapabilities Macro
    ' Macro recorded 27/05/2009
    '
    '
        ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
            , AllowSorting:=True, AllowFiltering:=True
    End Sub
    Good Luck. Hope this helps.
    Mark
    I have tried as you suggested & when I protect the sheet and try to sort with autofilter it says:
    The cell or chart you are trying to change is protected and therefore read only. etc.

    As for the Macro... I pasted it in the sheet code via alt F11.

    Am I missing something?
    I am running Excel 2010 64-bit on Windows 7 Ultimate 64-bit.

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Sudbury, Ontario Canada
    Posts
    1,360

    Default Re: Protect Sheet & still use the sort with Autofilter

    Hi:

    Is your AutoFilter already on BEFORE you protect your sheet or are you trying to set it up after the sheet is protected? Do you have other VBA Code in your sheet? Is one of the VBA Codes suppose to set up and activate your AutoFilter?

    On my spreadsheet I have the AutoFilter set up and on and then my sheet is protected and my AutoFilter still works the way it was set up. Maybe if you can provide a little more detail then I may be able to assist further. Sorry for the confusion...

    If worse comes to worse maybe you could email (if it is not confidential) the spreadsheet and I could have a look. Hopefully you can get this working for you.

    Bye 4 Now,
    Mark

  5. #5
    New Member
    Join Date
    Dec 2005
    Posts
    44

    Default Re: Protect Sheet & still use the sort with Autofilter

    Hi - I have exactly the same problem (using 2003). Autofilter is set before protection, and when protecting, I tick to allow users to Sort and to Use Autofilter. After protection, the filter options work in all ways except one - it won't sort by Ascending or Descending.

    Any ideas any-one?

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Sudbury, Ontario Canada
    Posts
    1,360

    Default Re: Protect Sheet & still use the sort with Autofilter

    Hi Sue:

    My understanding is that the Sort will not work unless you Unprotect the sheet (even when AutoFilter has been set up). From what I understand you would have to Sort using a macro that would Unprotect, sort, and the re-protect your sheet. Maybe I am wrong and someone else can jump in and correct me...

    Are you only looking to sort on a specific column or are there multiple sorts that are required?

    If the Board knows all your needs you may get some suggestions.

    Have a GREAT day,
    Mark

  7. #7
    New Member
    Join Date
    Dec 2005
    Posts
    44

    Default Re: Protect Sheet & still use the sort with Autofilter

    Thanks for that Mark. The specific purpose I am using this for does not warrant going to the extent of setting up a macro, and the protection is only to prevent accidental overwriting of formulas, so I will just show this user how to unprotect when needed. However, I will probably want to find a way around this in the future, so will post again at that stage.

    Thanks for your reply. Sue

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com