sorting a protected worksheet

[patriciaanne]

Hi
I am trying to sort a protected Excel worksheet. I unlocked the cells in the column I want to sort and then protected the worksheet. I selected "Select unlocked cells" and then selected "sort" before protecting the rest of the worksheet.
I can change the numbers in the unlocked column but it won't let me sort. It says it is read-only and to unprotect the worksheet.
Has anyone run into this and found a solution?
Thanks.

To everything there is a season, a time for every purpose under the sun.

 

[SkipVought]

Hi,

In this case it seems that you must unprotect the sheet in order to sort ALL the columns, although, there is a setting to allow sort on a protected sheet, under certain conditions,

Turn on your macro recorder and perform the unprotect, sort and protect.

Then execute this sort using a button or keyboard shortcut to run your recorded macro.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[combo]

Is the adjacent column not empty and contains locked cells? In this case excel tries to sort whole table, locked cells cause an error.

combo

 

[patriciaanne]

Thanks but Skip this spreadsheet is used by a number of people so the protection has to stay on.
And yes, there is adjacent data in the next column that is locked. So, why on earth would Excel allow you to sort a protected spreadsheet if all the columns have to be unprotected first?


To everything there is a season, a time for every purpose under the sun.

 

[combo]

Either make the surrounding area empty or tell users to select area to sort first and apply sorting with menu (data>sort).

combo

 

[GSCaupling]

Do all the users need to sort? Even if they do, wouldn't Skip's macro solution still work for everyone?

 

[SkipVought]

"Thanks but Skip this spreadsheet is used by a number of people so the protection has to stay on."

I'm not suggesting in any way that the protection be removed AFTER the macor runs. The UNPROTECT and PROTECT need to run IN THE MACRO.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[buitenhek]

Can you make a tertiary sheet from the protected sheet with just the sort data, lock the sheet with sort capabilities?

Regards
Peter Buitenhek
ProfitDeveloper.com

"Never settle for a job well done...always look for cost cutting measures

 

[patriciaanne]

Thanks Skip... sorry I misunderstood what you said.. that should work... thanks

To everything there is a season, a time for every purpose under the sun.