Excel Hacks Ebook [Electronic resources] نسخه متنی

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.