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/&command;-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.
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. The Go To Special dialog
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
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.
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 2. 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
If no password is used, omit Password:="Secret".
If a password is used, change the word Secret to
your password. Press Alt/&command;-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.