Problem acessing 'sort' in protected view

[oliviar]

Hello Saviours,
Every time I protect a sheet, I can no longer sort any values in my tables.
I check the box saying that users should be able to sort. But no luck.

Even if I say we can do EVERYTHING to the sheet (and it's still protected) - it still says I cannot sort while the sheet is protected.

The error message says exactly: "The cell of chart that 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...."

I'm at my wit's end. I don't understand.

I am using Excel 2007.

 

[pedie]

If you are sorting by vba then use something like this

[B]Sub MyMacro()[/B]
Sheet1.Unprotect Password:="Secret"
 'YOUR CODE
 Sheet1.Protect Password:="Secret"
[B]End Sub[/B]

or if you have locked ur sheet with password then select table area/range hi
ctrl + 1 then uncheck the protect checkbox so that the area remains unprotected for sorting...

I think it should work this way..

 

[oliviar]

Hi Pedie,
That still does not work. It won't sort the cells if they are locked OR unlocked. It won't let me sort AT ALL unless the sheet is unprotected.

 

[SydneyGeek]

If you put sorting code where Pedie has the YOUR CODE line it will work.

You need to use the correct password (ie, the one that you protected the sheet with in the first place instead of the word "Secret") so that the sheet can be unprotected, sorted and protected again.

Denis

 

[oliviar]

Thanks guys, but I think I'm missing something still.

This sheet HAS TO be protected when its distributed. Its not an option for it to go out unprotected or with the password to unprotect it buried in code somewhere. Also I cannot expect the users of this sheet to be able to run macros.

Are you guys telling me that while it is protected, it will NOT sort, even when I tell it that I want users to be able to sort when the sheet IS protected?
Is this is bug in excel? Because it has 'sort' explicitly as an option in the 'what do you want users to be able to do when you protect this sheet' screen.

 

[SydneyGeek]

It looks like you may be stuck. I tried a few things and, unless the cells are unlocked and you select them before sorting, you can't sort in a protected sheet. Locked cells can't be sorted, as far as I can see.

So, macros or no sorting, seems to be the answer. (Tested in 2010)

Denis

 

[oliviar]

What a pain. Why does excel offer the 'sort' option in the protect sheet page then? I would have assumed it would override the restrictions on sorting protected sheets and locked cells.

I might send feedback to MS about it.

 

[SydneyGeek]

Yeah, I agree. It's worth sending feedback about.

I did a quick search and the consensus is that the code workaround (mentioned above) is the way to go.

Denis

 

[bombuhdeer]

Hopefully this is not too late to help anyone. I use Excel 2010 and the key is to ensure the column headings are included in the range to be sorted, i.e. the cells with the Autofilters. Starting with an uprotected worksheet enable Filter for the headers, select the range to be sorted (including the headers), uncheck the Locked checkbox (Format Cells/Protection tab), select Protect Sheet and check Sort and Use Autofilter, click OK. This finally solved the problem for me, hope it helps everyone who doesn't want to, or can't, use macros.

 

[Iggy]

Hopefully this is not too late to help anyone. I use Excel 2010 and the key is to ensure the column headings are included in the range to be sorted, i.e. the cells with the Autofilters. Starting with an uprotected worksheet enable Filter for the headers, select the range to be sorted (including the headers), uncheck the Locked checkbox (Format Cells/Protection tab), select Protect Sheet and check Sort and Use Autofilter, click OK. This finally solved the problem for me, hope it helps everyone who doesn't want to, or can't, use macros.

Except by un-checking the "Locked" check box you completely unprotected the cell(s). So all the data can be edited - deleted - whatever.

Certainly seems like a bug in Excel.