Protect sheet but allow sorting only

[Christina]

I'm using Excel 2003, and I've tried to allow only sorting on a protected
sheet. I've tried by choosing Tools, Protection, Protect Sheet. Next, I've
checked the sort check box in the "Allow all users of this worksheet to:"
area. The only other check boxes that are checked are the "Select locked
cells" and "Select unlocked cells" boxes. When I test the protected
worksheet I am still unable to sort. Am I missing something?

Thanks!

 

[aglet]

I'm using Excel 2003, and I've tried to allow only sorting on a protected
sheet. I've tried by choosing Tools, Protection, Protect Sheet. Next,
I've
checked the sort check box in the "Allow all users of this worksheet to:"
area. The only other check boxes that are checked are the "Select locked
cells" and "Select unlocked cells" boxes. When I test the protected
worksheet I am still unable to sort. Am I missing something?

Thanks!

You have to first unlock the cells in the range you want sorted:
format | cells | protection | uncheck locked

But that will also allow the user to change the data in those cells.

 

[Christina]

I appreciate your response, however, I don't want anyone to be able to edit
the data within that range. Is there a way to allow the user to only sort
when the sheet is protected?

Thanks for your help!

 

[aglet]

The only thing I can think of is to use a macro to unprotect, sort, and then
re-protect the worksheet. I'm 99% sure that the cells themselves must be
unprotected in order to sort them. But maybe someone else here knows
better.

 

[Gord Dibben]

Correct.

The range to be sorted must be unlocked before protecting the sheet.

The macro to unprotect, sort and re-protect suggestion is the usual workaround.

The range to be sorted and the criteria are designated in the macro.

Sub sort()
ActiveSheet.Unprotect Password:="justme"
your sort code
ActiveSheet.Protect Password:="justme"
End Sub


Gord Dibben MS Excel MVP