Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Protect worksheet? Excel 2010

Posted on 2011-02-10
10
Medium Priority
?
785 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
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.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

609 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