Solved

Protect worksheet? Excel 2010

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

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

896 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

16 Experts available now in Live!

Get 1:1 Help Now