Enable Sorting A-Z in Locked Excel 2010 Workbook

I have a workbook with a tab for each day of the month. This workbook is placed in a shared file and used by various yards in different locations. A new yard beginning the process is refusing to use it because due to formulas we must lock the workbook (contains many formulas and a live clock macro to track trip time) and they cannot sort A-Z. The sheets are labeled 1,2,3,4,5,6,7,8,9 and so on to 31. I know that you can write a Macro to allow sorting, but I cannot get it to work. the users CANNOT know the password due to hidden revenue etc. and formulas.

First, you should know that Excel workbooks aren't the most secure things in the world and anyone who really wants to crack one really can through any number of simple methods.

That said, you can still totally do what you want. The exact code would depend on the dimensions of your worksheet, but it would look something like this:

Please Login or Register  to view this content.

This is sorting B4:B6 in ascending order, which would be assigned to a button. To hide the exposed password, you'd then go to Tools-VBA Project Properties, and assign a password to the code to protect anyone from being able to access it.

This code unprotects, sorts, reprotects.

Oh, we can definitely make this prettier I was just throwing it out there.

You could make 3 buttons, for example. Button 1 is below, and sorts B4:D9 but only by Column B. In my example, I had formulas in E, F, & G that were left unmoved.

I put the button at the top of Column B and relabeled it "Sort"

Please Login or Register  to view this content.

This code now specifically targets only the page the button is on. Now, maybe make 2 more buttons for Columns C and D, and alter the Key:=Range(" to target whatever column you want sorted by instead. Put those at the top of their respective columns. Copy those three buttons into each page. Shazam!

Here's a sample. The password to view the code is "trucks" which I've protected so you can see what others see if they try to look at the VB.

No problem. I use this type of thing often to make projects idiot-proof so people can't mess them up, but still able to manipulate parts as needed.

Good for calendars (to change year or month), employeee reviews, etc.