Page 244 - Excel Workbook for Dummies

24_798452 ch17.qxp 3/13/06 7:39 PM Page 227
Chapter 17: Protecting the Spreadsheet 227
Doing Data Entry in a Protected Worksheet

When protecting a worksheet, you can easily set it up so that you and your users can
jump right to the unlocked cells and avoid ever having to deal with the locked ones
(that you can’t change anyway) by using the Tab and Shift+Tab keys to navigate the
worksheet. All you have to do is format the cells that need changing as unlocked and
then remove the check mark from the Select Locked Cells check box in the Protect
Sheet dialog box when you turn on the worksheet protection.
When you press the Tab key in such a protected worksheet, Excel jumps the cell pointer
to the next unlocked cell to the right of the current one in that same row. When you
reach the last unlocked cell in that row, the program then jumps to the first unlocked
cell in the rows below. To move back to a previous unlocked cell, press Shift+Tab.
When Excel reaches the last unlocked cell in the spreadsheet, it automatically jumps
back to the very first unlocked cell on the sheet.

Of course, provided that you haven’t changed the behavior of the Enter key on the Edit
tab of the Options dialog box (Tools➪Options), you can also use the Enter key to move
down the columns instead of across the rows. Keep in mind, however, that pressing
the Enter key to progress down a column selects locked cells in that column as well as
the unlocked ones, whereas Tab skips all those with the Locked protection format.

Try It

Exercise 17-3: Doing Data Entry in a Protected Worksheet

Open Exercise17-3.xls in your Chapter 17 folder in the My Practice Spreadsheets folder
on your hard disk or in the Excel Workbook folder on the workbook CD-ROM. This
workbook contains a copy of the Spring Sale worksheet that is missing the furniture
description and retail price information (all the other data is entered along with the
formulas that determine the sale price). Use the sales table in this worksheet to prac-
tice doing data entry in a protected worksheet where your movements are restrained
to just the unlocked cells in the sheet:

1. Select the cell range C4:D9 in the Spring Sale worksheet and then open the
Format Cells dialog box (Ctrl+1), click the Protection tab, and deselect the
Locked check box before you select OK.
You unlock these cells because you still need to be able to make data entries in
them after you turn on the worksheet protection.
2. Open the Protect Sheet dialog box (Tools➪Protection➪Protect Sheet) and here
deselect the Select Locked Cells check box before you select OK (don’t bother to
assign a password to unprotect the worksheet).
After turning on worksheet protection with this check box unselected, Excel con-
strains the movement of the cell cursor to just the unlocked cell range C4:C9 in
the worksheet.
3. Press the Tab key repeatedly until you’ve moved the cell cursor through each of
the cells in the unlocked cell range, C4:C9.
4. Click cell A1 to attempt to locate the cell cursor in the Home cell of the Spring
Sale worksheet.
Note that clicking any cell outside of the unlocked cell range C4:C9 is no longer
allowed as long as worksheet protection is in effect.