Software

Sort and format a protected Excel worksheet with a macro

Sorting data in a protected worksheet can be accomplished only if the data is first unprotected. The key to overcoming this limitation lies with a macro and Visual Basic for Applications.

Problem

Reading a Tech Q&A post by travel advocate, I was reminded of the axiom: You can't always get what you want. That pretty much sums up the situation when it comes to sorting data in a Microsoft Excel protected worksheet. It is an immutable fact that only cells that are unprotected can be sorted. The problem is, in some circumstances, you may want to give users the ability to sort data without also giving them the ability to change actual data values. Or, in one possible variation, you may want to give users permission to add data and sort the results, but not permission to change previously entered data.

Solution 1

The most complete solution to this problem involves the power and flexibility provided by the built-in Visual Basic for Applications macro scripting language found in Excel. The best way to demonstrate the solution is through an example.

Example A

Here is a simple worksheet in Excel.

Example A

Unprotected Excel worksheet

We want to protect all of the previous test scores, but we also want to add test scores for Test5 when the time comes. We can accomplish this by following these steps:

1. Highlight F2:F9 and right-click.

2. Navigate to Format Cells in the dialog box.

3. Click on the Protection tab.

4. Uncheck the Locked box and click OK.

5. Go to Tools | Protection | Protect Sheet to protect the worksheet.

Note that all the worksheet data is protected, but the user is still allowed to enter data for Test5.

Example B

Now that the scores have been entered for Test 5, we want to sort the table listing the highest grades down to the lowest.

Example B

Worksheet before running the macro

But we can't sort a protected worksheet. and we don't want to allow users access to the previous test scores. Therefore, we must unprotect it, sort, and then reprotect it. We accomplish this through a simple macro:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 6/15/2004 by Mark W. Kaelin
'
'
    ActiveSheet.Unprotect
    Range("A1:G9").Sort Key1:=Range("G2"), Order1:=xlDescending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

Example C

After running the macro, you get a sorted worksheet that is still protected.

Example C

A sorted worksheet

The basic principles of this solution can be applied to other situations where you want to allow users to manipulate or format data without actually changing the data values themselves.

Solution 2

The alternative solution is much less sophisticated, but it can serve a purpose similar to that of Solution 1 in certain circumstances. If you have a worksheet with data that needs to be preserved, you can save the worksheet as read-only. Users can load the worksheet and manipulate the data as they see fit, but they cannot save their modifications to the original worksheet. In many cases, maintaining the original data is all that matters, and this solution accomplishes that task without adding the complexities of the previous solution.

Next Steps: Build your skills with these hand-picked resources
>Microsoft documentation: AllowSorting property
>Using the Protection Object to Protect Your Worksheets
>Super-Easy Guide to the Microsoft Office Excel 2003 Object Model

About Mark Kaelin

Mark W. Kaelin has been writing and editing stories about the IT industry, gadgets, finance, accounting, and tech-life for more than 25 years. Most recently, he has been a regular contributor to BreakingModern.com, aNewDomain.net, and TechRepublic.

Editor's Picks