Leading the way in Microsoft Office Development

Step 1 - What will it Protect ?

If a worksheet has any part of it protected then you cannot (may not be able) to select all the cells and perform a paste special as values.

All cells will be protected when you protect a worksheet. You must unlock the cells using (Format > Cells)(Protection tab) and clear the "locked" check box.

If you protect a worksheet without specifically unlocking any individual cells then every cell will be protected.

It is more than likely that you may want to protect certain formulas and formatting but leave other cells available for data to be entered. Before you protect the worksheet you must change the protection properties of those individual cells using (Format > Cells)(Protection tab).

If you have any hidden worksheets then these can be protected by protecting the workbook. Protecting a workbook will disable the (Format > Sheet > Unhides) command.

Step 2 - Protecting a Worksheet

You can protect an individual worksheet by selecting (Tools > Protection > Protect Sheet).

If you do not supply a password then anyone can unprotect the worksheet.

Protect worksheet and contents of locked cells - This must be checked.

All the following options will apply to all users of the worksheet.

Select locked cells - Allows all the users to select the cells which are protected.

Select unlocked cells - Allows all the users to select the cells which are not protected.

Format cells - Allows all the users to format the cells to any desired format.

Format columns - Allows all the users to format the columns to any desired format.

Format rows - Allows all the users to format the rows to any desired format.

Insert columns - Allows all the users to insert additional columns.

Insert rows - Allows all the users to insert additional rows.

Insert hyperlinks - Allows all the users to insert hyperlinks to other workbooks and internet addresses.

Delete columns - Allows all the users to delete any columns that do not contain locked cells.

Delete rows - Allows all the users to delete any rows that do not contain locked cells.

Sort - Allows all the users to sort any data on this worksheet.

Use AutoFilter - Allows all the users to use AutoFilter as a way to hide unwanted rows.

Use PivotTable reports - Allows all the users to use Pivot tables on the data.

Edit objects - Allows all the users to edit any objects including charts.

Edit scenarios - Allows all the users to use and edit the scenarios.

Step 3 - Unprotecting a Worksheet

You can unprotect an individual worksheet by selecting (Tools > Protection > UnProtect Sheet).

Type in the relevant password if required.

Step 4 - Things to Remember

If you have created a template and you want users to be able to tab between the cells that require data, you can unlock all the corresponding cells, before locking the worksheet.

By default all cells will be locked when a worksheet is protected.

If the sheet is protected you can hide the formulas from being displayed and displayed in the formula bar.

You can easily hide any worksheets and then protect the Workbook including “Structure”. Hide your sheets then press (Tools > Protection > Protect Workbook).

Before you protect a worksheet you need to remove the lock from any cells you want users to be able to change.

You can quickly move between unprotected cells on a locked worksheet by using the Tab key.