Results 1 to 9 of 9
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Not sorting under protection! (Excel XP)

    Here is a query I came accross in the last hour! If you password protect a list, allowing the user to sort, I see that the sorting ability is not allowed....even though I specify allow sorting? Is this a flaw in XP, or is it a flaw in my W/Books?
    The attached is a sample list. if you assign protection to it allowing the user to sort, and then sort the list, you will see that sorting is not allowed!!!!
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Not sorting under protection! (Excel XP)

    Only unlocked cells can be sorted. Before protecting the worksheet, select the used range (A1:H17), select Format | Cells..., activate the Protection tab, clear the Locked check box, then click OK.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Not sorting under protection! (Excel XP)

    OK....so thats where I went wrong. I was also allowing users the ability to insert rows and columns, and that worked fine without having to unlock cells. I just ran into the problem of sorting. But that clears it up.
    Many thanx again!
    Regards,
    Rudi

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Not sorting under protection! (Excel XP)

    It would be a bit difficult to unlock rows that have yet to be inserted by the user... <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Not sorting under protection! (Excel XP)

    <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15> I get the point! <img src=/S/yep.gif border=0 alt=yep width=15 height=15>
    Regards,
    Rudi

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not sorting under protection! (Excel XP)

    Similar problem, and I've followed your instructions, but it's still not working the way that I expected. On four worksheets, I have areas of 5-30 columns and several hundred rows formatted with a Style called "Input". Part of the Input specification is No Protection, so the cells are Unlocked. The formatted areas are proper Lists ie, they are bounded by blank rows and columns.

    Choosing one worksheet, I fill some bogus cells in a column to the right of the list area, being certain to leave at least one blank column. I can sort the list area as expected, and the sorting order of the cells beyond the list boundaries remains unaffected. Next, apply worksheet protection, allowing 4 items: select locked cell, select unlocked cell, allow sorting, allow autofilter. Finally, apply protection to the worksheet, and try to sort. Error message that the area is locked and cannot be sorted.

    Explicitly select a few cells from the Input area, and those few cells can be sorted. It appears that the sorting behaviour changes whether the worksheet is protected or not. When not protected, Excel automatically sets the extent of the sorting area as determined by the list boundaries. When protected, it tries to sort the entire row, ignoring any list boundaries.

    Is that the correct behaviour for Excel, or have I overlooked something? Excel 2002 SP3. Thanks.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not sorting under protection! (Excel XP)

    Looks like the "region select" command doesn't work with Protection on. With protection OFF, Ctrl-Shift-8 selects the region containing the cursor. With protection ON, it generates an error message.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Not sorting under protection! (Excel XP)

    As long as the current region of the active cell is contained entirely within the unlocked area of the protected worksheet, I can sort as usual by clicking one of the sort buttons on the toolbar. If I select Data | Sort..., the current region is selected.

    However, if the current region of the active cell extends into the locked area of the worksheet, I get an error message when I try to sort. If I explicitly select an area of unlocked cells, I can sort them (but I may get a warning about adjoining locked cells)

    The Select Current Region and Select Visible Cells commands are disabled in a protected worksheet (I have toolbar buttons for those commands)

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not sorting under protection! (Excel XP)

    Aha! It's the header row causing the problem on my worksheet. If I Unlock the header row, then the sorting works when Protection is on. Thanks.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

Posting Permissions

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