Solved

Protect worksheet? Excel 2010

Posted on 2011-02-10
10
759 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
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.

 

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

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.

Question has a verified solution.

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

This article will show you how to use shortcut menus in the Access run-time environment.
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

816 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now