Excel Hacks Ebook [Electronic resources]

Raina Hawley, David Hawley

نسخه متنی -صفحه : 136/ 29
نمايش فراداده

Hack 9 Lock and Protect Cells Containing Formulas

You may want to let users change cells containg data without providing them access to change formulas. You can keep cells containing formulas under lock and key without having to protect your entire sheet or workbook.

When we create a spreadsheet, most of us need to use formulas of some sort. Sometimes, however, you might not want other users to tamper/delete/overtype any formulas you included on your spreadsheet. The easiest and most common way of barring people from playing with your formulas is to protect your worksheet. However, protecting your worksheet doesn't just prevent users from tampering with your formulas, it also stops users from entering anything at all. Sometimes you do not want to go this far.

By default, all cells on a worksheet are locked; however, this has no effect unless worksheet protection has been applied. Here is a very easy way to apply worksheet protection so that only formula cells are locked and protected.

Select all cells on your worksheet, either by pressing Ctrl/-A or by clicking the gray square at the intersecting point of column A and row 1. Then select Format Cells Protection and uncheck the Locked checkbox to remove the tick. Click OK.

Now select any single cell, select Edit Go To... (Ctrl-G or F5), and click Special. You'll see a dialog box such as that in Figure 1-13.

Figure 1-13. The Go To Special dialog

Select Formulas from the Go To Special dialog and, if needed, limit the formulas to the subtypes underneath. Click OK. With only the formula cells selected, select Format Cells Protection and check the Locked checkbox to insert a tick. Select OK. Now select Tools Protection Protect Worksheet to protect your worksheet and apply a password if required.

The preceding method certainly saves a lot of time and eliminates possible errors locating formulas so that you can protect them. Unfortunately, it can also prevent users from using certain features, such as sorting, formatting changes, aligning text, and many others you might not be concerned with, even when in an unlocked cell. You can overcome this problem in two ways.

The first approach doesn't use worksheet protection at all, and uses data validation instead.

Data validation is far from bulletproof when it comes to preventing users from entering nonvalidated data into cells. Users can still paste into a validated cell any data they want and, in doing so, remove the validation from that cell unless the copied cell also contains data validation, in which case this validation would override the original validation.

To see what we mean, select any single cell, select Edit Go To... (Ctrl-G or F5), and click Special. Now select Formulas from the Go To Special dialog and, if needed, limit the formulas to the subtypes underneath. Click OK.

With only the Formula cells selected, select the Data Validation Settings page tab, select Custom from the Allow: box, and in the Formula box, enter ="", as shown in Figure 1-14. Click OK.

Figure 1-14. Validation formulas

This method will prevent a user from accidentally overtyping into any formula cells although, as stressed in the earlier warning, it is not a fully secure method and should be used only for accidental overtyping, etc. However, the big advantage to using this method is that all of Excel's features are still usable on the worksheet.

The last method also will enable you to use all of Excel's features, but only when you are in a cell that is not locked. To start, ensure that only the cells you want protected are locked and that all other cells are unlocked. Right-click the Sheet Name tab, select View Code from the pop-up menu, and enter the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Locked = True Then
Me.Protect Password:="Secret"
Else
Me.Unprotect Password:="Secret"
End If
End Sub

If no password is used, omit Password:="Secret". If a password is used, change the word Secret to your password. Press Alt/-Q or click the X in the top righthand corner to get back to Excel and save your workbook. Now, each time you select a cell that is locked, your worksheet will automatically protect itself. The moment you select any cell that is not locked, your worksheet will unprotect itself.

This hack doesn't work perfectly, though it usually works well enough. The keyword used in the code, Target, will refer only to the cell that is active at the time of selection. For this reason, it is important to note that if a user selects a range of cells (with the active cell being an unlocked cell), it is possible for him to delete the entire selection because the target cell is unlocked and, therefore, the worksheet automatically will unprotect itself.