Protection Tutorial

[MAD]

I am using Excel 2003
I have a spreadsheet report that contains lots of formulas. There are
several people who have access to this spreadsheet. Lately a couple of my
formulas got mixed up. So I want to be able to lock up certain cells so that
no one but me can change them. Does anyone know where I can find a tutorial
on how to lock certain cells to keep them from being changed. I would
appreciate any help you can give me.

Aurora

 

[Dave Peterson]

First, worksheet protection is easily broken. So this won't stop anyone who
wants to unprotect the worksheet.

By default (unless you've changes something), each cell is locked. You can
toggle this setting by:
Select the range
Format|Cells|Protection tab|check or uncheck Locked

If you have lots of cells that should be unlocked, and just a few that should be
locked, you can select all the cells. Change all of them to what you want.
Then select the "opposite" group and change the setting to the other way.

But the lockedness of a cell doesn't really mean much until you protect the
sheet.

Tools|Protection|protect sheet
Give it a memorable password (even though this is easy to break, you don't want
to waste your time finding how).

Now test a few cells. The locked cells can't be changed by the user. The
unlocked cells can.

In xl2003, there's a bunch of options when you use that tools|protection|protect
sheet dialog.

You can allow the user to format cells, insert/delete rows or columns... I
think that these were added in xl2002. If you're supporting xl2k and below, you
won't want to use those -- no matter how tempting they may be!

=====
Now that you've seen how locked cells (and unlocked cells) behave on a protected
sheet, try doing some of the things that you expect the users to be able to do.

Lots of features are disabled on protected sheets and you may find that even
though the formulas are safe, other stuff won't work.

And if you share the workbook, the solutions to many of these problems disappear
-- it's take it or leave it.

 

[JLGWhiz]

Look in Excel help under "Protection" Or "Lock only a few cells on a
worksheet" Or "Overview of security and protection in Excel"

To access these, click the question mark in a cirlcle icon on the menu bar.
When the "Search Results" pane appears, type one of the above topics in the
"Search" window and press enter. Then select the topic from the list of
results.

 

[Chip Pearson]

By default, all cells are locked, but the locking is not in effect
until the worksheet is protected. Therefore, prior to protecting the
sheet, the cells are wide open.

If you want to lock all but a few cells, select those cells and
uncheck the Locked option on the Protection Tab of the Cell Formatting
dialog. Then protect the worksheet, supplying a password if desired.
At this point, all cells except those whose Locked property you clear
are locked. In addition to locking a cell, you can check the Hidden
property to prevent the formula of the cell from appearing in the
formula bar.

If you want to lock only a few cells and leave everything else
unlocked, you first need to unlock all the cells on the sheet. First,
select all the cells in the worksheet. You can do this by pressing
CTRL A twice or by clicking the Select All button (the square above
the "1" row header and to the left of the "A" column header -- it
isn't labeled; it appear only as a gray or blue box.). With all cells
selected, open the Cell Formatting dialog, choose the Protection tab,
and clear the Locked check box and press OK. Now, select those cells
that you do want to lock, open the formatting dialog and check the
Protect option. Finally, protect the worksheet.

To protect only formula cells and leave everything else unlocked,
select all cells as described above and then clear the Locked property
on the Protection tab of the Cell Format dialog. Then, press CTRL G to
open the Go To dialog, click the Special button, and choose the
Formals option. This will select all cells containing formulas. With
those cells selected, open the Cell Format dialog and check the Locked
property and, optionally, the Hidden property. Finally, protect the
worksheet.

If you need to change locked cells, you must first unprotect the
worksheet, supplying the correct password if a password is required.
Make your changes and then reprotect the sheet.

Remember, the Locked property of a cell has no effect unless the
worksheet is protected. If the sheet isn't protected, nothing is
locked, regardless of the Locked property.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com