Sort in a protected worksheet ?

**dunCar** · 03-24-2010, 12:51 PM

I am working in Excel 2003.

I want to lock the values and formats of all of the cells on the page, but still be able to sort/use an autofilter.

To do this I went to Tools --> Protection --> Protect Sheet
Then I checked "protect worksheet and contents of locked cell" (it won't let me hit ok without checking that)
Then under "allow all users of this worksheet to:"
And I selected, "Select locked cells", "Select unlocked cells", "Sort" & "Use AutoFilter"

From my logic and what I have read online, this should allow me to sort in my worksheet....
However, when I highlight what I want to sort and select the sort, I get the error message:
"The cell or chart you are trying to change is protected and therefore read-only. To modify a protected cell or chart, first remove protection using the Unprotect Sheet command."

What am I doing wrong?

**6StringJazzer** · 03-24-2010, 02:25 PM

Here is what I found, but I don't where this is documented. The "Sort" checkbox in Protect Sheet appears to apply to the "sort" options in the autofilter, rather than the Sort function. If you do not have the Sort checkbox checked, then any attempt to select a sort option from the autofilter menu will just be ignored. Further, even if you allow sorting, you still have to unlock the cells to be sorted.

This seems to be a confusing design but that's the best I can do in explaining how it works. I cannot figure out how to leave cells locked, yet still allow sorting.

It would be possible to do this with VBA. The VBA would unprotect the sheet, do the sort, and then re-protect the sheet. You can record a macro to see how it would work. Depends on what kind of filtering/sorting you need to support.

Sort in a protected worksheet ?

LinkBack

Thread Tools

Rate This Thread

Display

Sort in a protected worksheet ?

Re: Sort in a protected worksheet ?

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions