Overview
The topic of "protecting" a spreadsheet receives a great deal of attention in the Excel newsgroups. This document provides answers to some common questions.
Excel provides three primary ways to protect information in a spreadsheet:
Worksheet Protection
Questions in this section deal with protecting the contents of cells and objects on a worksheet.
How do I protect a worksheet?
Activate the worksheet to be protected, then choose Tools - Protection - Protect Sheet. You will be asked to provide a password (optional). If you do provide a password, that password will be required to unprotect the worksheet.
I tried the procedure outlined above, and it doesn't let me change any cells! I only want to protect some of the cells, not all of them.
Every cell has two key attributes: Locked and Hidden. By default, all cells are locked, but they are not hidden. Furthermore, the Locked and Hidden attributes come into play only when the worksheet is protected. In order to allow a particular cell to be changed when the worksheet is protected, you must unlock that cell.
How do I unlock a cell?
- Select the cell or cells that you want to unlock.
- Choose Format - Cells
- In the Format Cells dialog box, click the Protection tab
- Remove the checkmark from the Locked checkbox.
Remember: Locking or unlocking cells has no effect unless the worksheet is protected.
How do I hide a cell?
- Select the cell or cells that you want to unlock.
- Choose Format - Cells
- In the Format Cells dialog box, click the Protection tab
- Add a checkmark to the Hidden checkbox.
Remember: Changing the Hidden attribute of a cell has no effect unless the worksheet is protected.
I made some cells hidden and then protected the worksheet. But I can still see them. What's wrong?
When a cell's Hidden attribute is set, the cell is still visible. However, it's contents do not appear in the Formula bar. Making a cell Hidden is usually done for cells that contain formulas. When a formula cell is Hidden and the worksheet is protected, the user cannot view the formula.
I protected my worksheet, but now I can't even do simple things like sorting a range. What's wrong?
Nothing is wrong. That's the way worksheet protection works. Unless you happen to be using Excel 2002.
How is worksheet protection different in Excel 2002?
Excel 2002 provides you with a great deal more flexibility when protecting worksheets. When you protect a worksheet using Excel 2002, you are given a number of options that let you specify what the user can do when the worksheet is protected:
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Why aren't these options available in earlier versions of Excel?
Good question. Only Microsoft knows for sure. The limitations of protected worksheets have been known (and complained about) for a long time. For some reason, Microsoft never got around to addressing this problem until Excel 2002.
Can I lock cells such that only specific users can modify them?
Yes, but it requires Excel 2002.
How can I find out more about the new protection options available in Excel 2002?
Start with Excel's Help system. If you're a VBA programmer, you may be interested in this MSDN article that discusses the Protection object.
Can I set things up so my VBA macro can make changes to Locked cells on a protected sheet?
Yes, you can write a macro that protects the worksheet, but still allows changes via macro code. The trick is to protect the sheet with the UserInterfaceOnly parameter. Here's an example:
ActiveSheet.Protect UserInterfaceOnly:=TrueAfter this statement is executed, the worksheet is protected -- but your VBA code will still be able to make changes to locked cells and perform other operation that are not possible on a protected worksheet.
If I protect my worksheet with a password, is it really secure?
No. Don't confuse protection with security. Worksheet protection is not a security feature. Fact is, Excel uses a very simple encryption system for worksheet protection. When you protect a worksheet with a password, that password -- as well as many others -- can be used to unprotect the worksheet. Consequently, it's very easy to "break" a password-protected worksheet.
Worksheet protection is not really intended to prevent people from accessing data in a worksheet. If someone really wants to get your data, they can. If you really need to keep your data secure, Excel is not the best platform to use.
So are you saying that protecting a worksheet is pointless?
Not at all. Protecting a worksheet is useful for preventing accidental erasure of formulas. A common example is a template that contains input cells and formulas that calculate a result. Typically, the formula cells would be Locked (and maybe Hidden) the input cells would be Unlocked, and the worksheet would be protected. This helps ensure that a novice user will not accidentally delete a formula.
Are there any other reasons to protect a worksheet?
Protecting a worksheet can also facilitate data entry. When a worksheet is locked, you can use the Tab key to move among the Unlocked cells. Pressing Tab moves to the next Unlocked cell. Locked cells are skipped over.
OK, I protected my worksheet with a password. Now I can't remember the password I used.
First, keep in mind that password are case-sensitive. If you entered the password as xyzzy, it won't be unprotected if you enter XYZZY.
Here's a link to a VBA procedure that may be able to derive a password to unprotect the worksheet. This procedure has been around for a long time, and is widely available -- so I don't have any qualms about reproducing it here. The original author is not known.
If that fails, you can try one of the commercial password-breaking programs.
How can I hide a worksheet so it can't be unhidden?
You can designate a sheet as "very hidden." This will keep the average user from viewing the sheet. To make a sheet very hidden, use a VBA statement such as:
Sheets("Sheet1").Visible = xlVeryHiddenA "very hidden" sheet will not appear in the list of hidden sheets, which appears when the user selects Format - Sheet - Unhide. Unhiding this sheet, however, is a trivial task for anyone who knows VBA.
Can I prevent someone from copying the cells in my worksheet and pasting them to a new worksheet?
Probably not. If someone really wants to copy data from your worksheet, they can find a way.
Workbook Protection
Questions in this section deal with protecting workbooks.
What types of workbook protection are available?
Excel provides three ways to protect a workbook:
- Require a password to open the workbook
- Prevent users from adding sheets, deleting sheets, hiding sheets, and unhiding sheets
- Prevent users from changing the size or position of windows
How can I save a workbook so a password is required to open it?
Choose File - Save As. In the Save As dialog box, click the Tools button and choose General Options to display the Save Options dialog box, in which you can specify a password to open the file. If you're using Excel 2002, you can click the Advanced button to specify encryption options (for additional security). Note: The exact procedure varies slightly if you're using an older version of Excel. Consult Excel's Help for more information.
The Save Options dialog box (described above) also has a "Password to modify" field. What's that for?
If you enter a password in this field, the user must enter the password in order to overwrite the file after making changes to it. If the password is not provided, the user can save the file, but he/she must provide a different file name.
If I require a password to open my workbook, is it secure?
It depends on the version of Excel. Password-cracking products exist. These products typically work very well with versions prior to Excel 97. But for Excel 97 and later, they typically rely on "brute force" methods. Therefore, you can improve the security of your file by using a long string of random characters as your password.
How can I prevent a user for adding or deleting sheets?
You need to protect the workbook's structure. Select Tools - Protection - Protect Workbook. In the Protect Workbook dialog box, make sure that the Structure checkbox is checked. If you specify a password, that password will be required to unprotect the workbook.
When a workbook's structure is protected, the user may not:
- Add a sheet
- Delete a sheet
- Hide a sheet
- Unhide a sheet
- Rename a sheet
- Move a sheet
How can I distribute a workbook such that it can't be copied?
You can't.
VB Project Protection
How can I prevent others from viewing or changing my VBA code?
If you use Excel 97 or later... Activate the VB Editor and select your project in the Projects window. Then choose Tools - xxxx Properties (where xxxx corresponds to your Project name). In the Project Properties dialog box, click the Protection tab. Place a checkmark next to Lock project for viewing, and enter a password (twice). Click OK, then save your file. When the file is closed and then re-opened, a password will be required to view or modify the code.
What if I'm still using Excel 5/95?
You can hide your VBA module sheets and protect the workbook's structure. Or, you can create an add-in from your workbook.
Is my Excel 5/95 add-in secure?
Not at all. It's very easy (trivial, in fact) to convert such an add-in to a standard workbook. However, the casual user won't know how to do this.
Is my Excel 97 (or later) add-in secure?
The type of VB Project protection used in Excel 97 and later is much more secure than in previous versions. However, several commercial password-cracking programs are available. These products seem to use "brute force" methods that rely on dictionaries of common passwords. Therefore, you can improve the security of your file by using a long string of random characters as your password.
Can I write VBA code to protect or unprotect my VB Project?
No. The VBE object model has no provisions for this -- presumably an attempt to thwart password-cracking programs. It may be possible to use the SendKeys statement, but it's not completely reliable.