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
>” width=”8″ height=”10″ align=”absmiddle”><br />
<a target=Microsoft
documentation: AllowSorting property
>” width=”8″ height=”10″ align=”absmiddle”><br />
<a target=Using the
Protection Object to Protect Your Worksheets
>” width=”8″ height=”10″ align=”absmiddle”><br />
<a target=Super-Easy Guide
to the Microsoft Office Excel 2003 Object Model