Welcome toExcel.Tips.Net
Tips.Net Home
ExcelTips Home
Ask an Excel Question
Make a Comment
ExcelTips FAQ
ExcelTips Premium
Learn Access Now
Free Printable Forms
Beauty Tips
Car Tips
Cleaning Tips
College Tips
Cooking Tips
Excel2007 Tips
ExcelTips
Family Tips
Gardening Tips
Health Tips
Home Tips
Legal Tips
Money Tips
Organizing Tips
Pest Tips
Pet Tips
Wedding Tips
Word2007 Tips
WordTips
Advertise on the
ExcelTips Site
Filtering Columns for Unique Values
Printing Multiple Worksheets on a Single Page
When you protect a worksheet, Excel stops users from performing a wide variety of tasks on the data in the worksheet. One of the things that the user can no longer do is to sort data. What if you want the user to be able to sort data, but still have the sheet protected?
If you are using Excel 2002, Excel 2003, or Excel 2007 the answer is quite easy: These versions of Excel allow you to specify what users can and cannot do with a protected worksheet. When you choose Tools | Protection | Protect Sheet (Excel 2002 and Excel 2003) or display the Review tab of the ruler and click Protect Sheet in the Changes group (Excel 2007), Excel displays the Protect Sheet dialog box. At the bottom of the dialog box is a long list of check boxes. All you need to do is select what the user should be able to do with the worksheet. One of the options (you need to scroll down a bit) is Sort. If you select this option, then users can sort protected data.
If you are using an older version of Excel, the solution is to create a macro that unprotects the worksheet, sorts the data, and then protects the worksheet again. The following is a simple example:
Sub Sorting()
ActiveSheet.Unprotect
Range("A1:D100").Sort Key1:=Range("A1"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
ActiveSheet.Protect
End Sub
This example sorts the data in the range A1:D100 based on the contents of column A. The macro illustrates the general concept behind this approach, but you will need to modify it to reflect the needs of your data and your users.
If you go the macro route, you need to assign the macro to either a toolbar button or a menu command. If you don't the user will never be able to use it, since the Macros menus are disabled in a protected document.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2458) applies to Microsoft Excel versions: 97 2000 2002 2003 2007
A Picture is Worth Thousands! Your worksheets are not limited to holding numbers and text. You can also add graphics or easily create charts based on your data. Excel Graphics and Charts, available in two versions, helps you make your graphics and charts their absolute best.