?
Solved

Protect worksheet? Excel 2010

Posted on 2011-02-10
10
Medium Priority
?
788 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:Farah Mohammed
  • 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:Farah Mohammed
ID: 34866672
But if I left the cells unlocked, someone could change the data, right?
0
 
LVL 50
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Farah Mohammed
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 (Microsoft MVP / EE MVE) earned 1000 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 1000 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:Farah Mohammed
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:Farah Mohammed
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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

With its various features, Office 365 can not only help you with your day-to-day business tasks, it can also do wonders for your marketing campaign.
In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

840 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