Results 1 to 5 of 5

Thread: Sorting a table in a protected sheet when some cells are locked
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jan 2015
    Location
    Calgary
    Posts
    14
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Sorting a table in a protected sheet when some cells are locked

    I have a form which people will use to enter data in a standardized way.
    The table has columns A:C as formulae and columns D:G as empty cells ready for people to add the necessary data using a mix of Data Validated pick lists or free-form text.
    I've locked columns A:C to prevent people accidentally overwriting the formulae. Columns D:G are unlocked.
    I've then protected the sheet.

    Sometimes people want to sort the table to put it into a more sensible order for them to eyeball the data before they send it into me.
    Even if I allow sorting when I set up the protection, it seems like a table with a mix of locked & unlocked cells can't be sorted.
    I could pop a button on the sheet attached to a macro that unprotects, sorts then reprotects but is there anything easier that could be done?

    I'd love to know if there is something nifty out there that I'm just not aware of.
    Many thanks in advance
    Teapotlid

  2. #2
    Board Regular RatExcel's Avatar
    Join Date
    Aug 2014
    Location
    Kraków, Poland
    Posts
    222
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sorting a table in a protected sheet when some cells are locked

    That's one of Excel disfunctionalities that should be fix long time ago but it is not. Even if you check Sort and Use Autofilter options when protecting the sheet you won't be able to sort. You can only filter the values. Sorting is contected with changing the sequence of rows which basicly is changing the cells. Maybe it's possible in Excel 2013 but for sure not in 2010.
    www.RatExcel.wordpress.com

    If you find my solution helpful, please Like my post


    Running Excel 2010 32-bit / Windows 7 64-bit

    Posting guidelines, forum rules and terms of use
    Try searching for your answer first, see how
    Read the FAQs

    List of BB codes

  3. #3
    New Member
    Join Date
    Jan 2015
    Location
    Calgary
    Posts
    14
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sorting a table in a protected sheet when some cells are locked

    Dang!
    Thanks Rat. I guess I'll just have to record a macro & pop a button on my form.
    Not the end of the world but it does seem a bit inelegant.

  4. #4
    New Member
    Join Date
    Apr 2016
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sorting a table in a protected sheet when some cells are locked

    Step 1: Make cells editable so that sorting will work.

    Add cells we want to sort to a range and make that range editable in “Allow Users to Edit Ranges.” This allows users to edit these cells when the worksheet is protected, even if they are locked cells.

    1.Select all the cells you would like the user to be able to sort, including their column headings.
    2.Go to the Data tab and click Filter. An arrow should appear next to each column header.
    3.Go to Review tab-> Allow Users to Edit Ranges 1.Click “New…”
    2.Give the range a title.
    3.“Refers to Cells” should already contain the cells you want to allowing sorting on.
    4.If you want to allow only certain people to sort, give the range a password.
    5.Click “OK”


    Step 2: Prevent users from editing these cells

    When protecting the worksheet, uncheck “Select Locked Cells” worksheet protection property. This will prevent users from editing the cells.

    1.In the “Allow Users to Edit Ranges” dialog: 1.Click “Protect Sheet…”
    2.Give the worksheet a password
    3.Uncheck the worksheet protection property called “Select Locked Cells”
    4.Check the “Sort” property and the “AutoFilter” properties
    5.Click “OK”


    This solution allows users to use the Auto Filter arrows in the column names or the Sort buttons in the Data tab to sort data. Another benefit is that you have the option of allowing only certain users to sort by giving the range a password. Please note that this range password is separate from the password you set to protect the sheet.

  5. #5
    New Member
    Join Date
    May 2019
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sorting a table in a protected sheet when some cells are locked

    I have tried this solution many times and can not get it work. Although the filtering works, the data range cells can be deleted (e.g. deleted) by the user. Any suggestions?

Some videos you may like

User Tag List

Tags for this Thread

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
  •