Protect worksheet? Excel 2010
I have an excel 2010 worksheet that I want to protect with a password as I do not want others to be able to change the data. However, I want them to be able to sort the spreadsheet as needed. When I go into Protect worksheet option, it gives me a list of items that I can check allow the users to do. I check the box for Sort, and then proceed to enter password, etc.
However, on testing, I open the worksheet, it will not allow me to sort. It tells me that the cell that I am trying to change is protected and therefore read only.
I have all the cells "locked".
Any ideas? Thank you
However, on testing, I open the worksheet, it will not allow me to sort. It tells me that the cell that I am trying to change is protected and therefore read only.
I have all the cells "locked".
Any ideas? Thank you
Rory Archibald
You can only sort unlocked cells, I'm afraid.
ASKER
But if I left the cells unlocked, someone could change the data, right?
Hello,
that "Sort" check box wins the prize for the most confusing setting in my book. It does not allow you to sort "locked" cells in a protected worksheet. It only allows sorting in "unlocked" cells in a protected sheet.
That's the explanation.
The solution may be with a macro that will unprotect, sort and re-protect.
cheers, teylyn
that "Sort" check box wins the prize for the most confusing setting in my book. It does not allow you to sort "locked" cells in a protected worksheet. It only allows sorting in "unlocked" cells in a protected sheet.
That's the explanation.
The solution may be with a macro that will unprotect, sort and re-protect.
cheers, teylyn
ASKER
You tell me about the Sort check box.
The macro sounds like a good idea, however, there is so many sort combination, wouldn't that be a nightmare?
The macro sounds like a good idea, however, there is so many sort combination, wouldn't that be a nightmare?
Yes. Worksheet protection is pretty feeble anyway though.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
With 2010 you could override the behaviour of the built-in buttons using custom XML, as long as you are using an xlsm or xlsb format file.
However, would that work if the user is on Excel 2007?
I think so, but I am not 100% sure.
It would still be tricky to handle the default sort buttons (i.e. the non-dialog ones) I think.
It would still be tricky to handle the default sort buttons (i.e. the non-dialog ones) I think.
ASKER
I think I may skip the buttons then. I will take a look at probably doing a macro of some sort.
Thank you rorya and tevlyn for your help and suggestions.
Thank you rorya and tevlyn for your help and suggestions.