Tips and Shortcuts 30 to 32 pg 215 216 217

30. Protecting the workbook

NOTE

In Excel, a workbook is a file. It may contain one or more sheets.

In a networking environment, protecting a file is important to avoid arbitrary changes to the file by other users. After we protect a workbook, we cannot add or remove a worksheet. To protect a workbook:

1. Click menu Tools/Protection/Protect Workbook. The Protect Workbook dialog box will appear.

Protect Workbook

Protect workbook for
	Structure
	Windows

Password (optional):

	OK		Cancel

2. Put a mark in the box at the left of the Structure option. It is better not to mark the Windows box. If you type a password in the Password box, you will have to remember the password to later unprotect the workbook. If you forget the password, you will not be able to unprotect the file.

3. To unprotect the workbook, click the menu Tools/Protection/Unprotect Workbook.

215

31. Protect cells containing the labels or formulas

We can protect the labels in the structure in a sheet:

1. Select all the cells in the relevant sheet. You do this by clicking on the button situated on the left of column A label and above the row 1 label.

2. Click menu Format/Cells... The Format Cells dialog box will appear. Click on tab Protection. Unmark the Locked and Hidden boxes. Click OK.

3. Select or highlight the labels that you want to protect. Click menu Format/Cells... The Format Cells dialog box will appear. Click on tab Protection. Mark the Locked box but ;leave the Hidden box unmarked. (If you mark the Hidden box, it will hide the formulas). Click OK.

4. Click menu Tools/Protection/Protect Sheet... The Protect Sheet dialog box will appear. Mark all three boxes: Contents, Objects, Scenarios. Leave the Password box blank or you can supply a password (If you supply a password, you must remember it, because if in future you want to unprotect the sheet, Excel will ask you for the password. You may want to unprotect to make changes to the labels). Click OK.

5. After this, if you or someone else try to change the protected labels, Excel will not allow it and will display a message to say that the label is protected. You also cannot delete the relevant row or column.

6. To unprotect, click menu Tools/Unprotect Sheet (This menu will not appear unless you have protected the sheet).

32. Using data validation to inform user that cell should be blank

Let's say we want to inform the user that cell A3 should be left blank. Follow this steps:

1. Activate the cell (in this case, A3). Make sure there is no single character in it, even a space.

2. Click menu Data/Validation.

216

3. The Data Validation dialog box will appear.

Data Validation

Settings	Input Message	Error Alert

Validation criteria
	Allow:
	Custom					Ignore blank

Data:
	between

Formula:
	=ISBLANK(A1)

Apply these changes to all other cells with the same settings

Clear All		OK		Cancel

4. Click on tab Settings. Under the Allow label, select Custom. Under the Formula label, write this formula:
=ISBLANK(A3)

5. Click on the tab Input Message. Remove the marking in the box at left of label Show input message when cells is selected.

6. Click on the tab Error Alert. Make sure there is a mark in the box at left of label Show error alert after invalid data is entered. Under the label Style, choose any option. Under the label Title, write a short message for the title of the error message. Under the momo label Error message, write a more detailed description. Click OK. 10

7. After this, if you try to write anything to cell A3, a message will appear.

217