Protect sheet but allow sorting only

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Christina - 04 Mar 2008 01:27 GMT

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!

Signature

Christina

Reply to this Message

aglet - 04 Mar 2008 03:44 GMT

> 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,
[quoted text clipped - 5 lines]
>
> 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.

Reply to this Message

Christina - 04 Mar 2008 12:56 GMT

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!

Signature

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,
[quoted text clipped - 10 lines]
>
> But that will also allow the user to change the data in those cells.

Reply to this Message

aglet - 04 Mar 2008 13:13 GMT

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.

>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
[quoted text clipped - 19 lines]
>>
>> But that will also allow the user to change the data in those cells.

Reply to this Message

Gord Dibben - 04 Mar 2008 17:01 GMT

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

>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
[quoted text clipped - 24 lines]
>>>
>>> But that will also allow the user to change the data in those cells.

Reply to this Message

Rate this thread: