Solved

Protect worksheet? Excel 2010

Posted on 2011-02-10
10
771 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:squirrelzan
  • 4
  • 4
  • 2
10 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34866653
You can only sort unlocked cells, I'm afraid.
0
 

Author Comment

by:squirrelzan
ID: 34866672
But if I left the cells unlocked, someone could change the data, right?
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 34866687
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
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Comment

by:squirrelzan
ID: 34866727
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?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34866748
Yes. Worksheet protection is pretty feeble anyway though.
0
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst earned 250 total points
ID: 34866762
You'd have to provide an interface of some kind, maybe with a user form, where the user can specify the sort parameters and then feed that to a sort statement in VBA.

0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 250 total points
ID: 34866782
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.
0
 

Author Comment

by:squirrelzan
ID: 34866806
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?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34866837
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.
0
 

Author Closing Comment

by:squirrelzan
ID: 34866899
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.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question