Chapter 31. Protecting Your Work

IN THIS CHAPTER

Protecting worksheets

Protecting workbooks

Protecting Visual Basic Projects

Other related topics

The concept of "protection" gets a lot of attention in the Excel newsgroups and forums. It seems that many users want to learn how to protect their workbooks from being copied or modified. Excel has several protection-related features, and those features are covered in this chapter.

31.1. Types of Protection

Excel's protection-related features fall into three categories:

• Worksheet protection: Protecting a worksheet from being modified, or restricting the modifications to certain users

• Workbook protection: Protecting a workbook from having sheets inserted or deleted, and also requiring the use of password to open the workbook

• Visual Basic (VB) protection: Using a password to prevent others from viewing or modifying your VBA code

Before I discuss these features, you should understand the notion of security. Using a password to protect some aspect of your work doesn't guarantee that it's secure. Password-cracking utilities (and some simple tricks) have been around for a long time. Using passwords work in the vast majority of cases, but if someone is truly intent on getting to your data, he or she can usually find a way. If absolute security is critical, perhaps Excel isn't the proper tool.

31.2. Protecting a Worksheet

You may want to protect a worksheet for a variety of reasons. One reason is to prevent yourself or others from accidentally deleting formulas or other critical data. A common scenario is to protect a worksheet so that the data can be changed, but the formulas can't be changed.

To protect a worksheet, activate the worksheet and choose Review Changes Protect Sheet. Excel displays the Protect Sheet dialog box shown in Figure 31.1. Note that providing a password is optional. If you enter a password, that password will be required to unprotect the worksheet. If you accept the default options in the Protect Sheet dialog box (and if you haven't unlocked any cells), none of the cells on the worksheet can be modified.

To unprotect a protected sheet, choose Review Changes Unprotect Sheet. If the sheet was protected with a password, you're prompted to enter that password.

Figure 31.1. Use the Protect Sheet dialog box to protect a worksheet.

31.2.1. Unlocking cells

In many cases, you will want to allow some cells to be changed when the worksheet is protected. For example, your worksheet may have some input cells that are used by formula cells. In such a case, you would want the user to be able to change the input cells, but not the formula cells. Every cell has a Locked attribute, and that attribute determines whether the cell can be changed when the sheet is protected.

By default, all cells are locked. To change the locked attribute, select the cell or range, right-click, and choose Format Cells from the shortcut menu (or press Ctrl+1). The Protection tab of the Format Cells dialog box opens (see Figure 31.2); clear the Locked check box and then click OK.

Figure 31.2. Use the Protection tab in the Format Cells dialog box to change the Locked attribute of a cell or range.

After you unlock the desired cells, choose Review Changes Protect Sheet to protect the sheet. After doing so, you can change the unlocked cells, but if you attempt to change a locked cell, Excel displays the warning alert shown in Figure 31.3.

Figure 31.3. Excel warns you if you attempt to change a locked cell.

31.2.2. Sheet protection options

The Protect Sheet dialog box has several options, which determine what the user can do when the worksheet is protected.

• Select Locked Cells: If checked, the user can select locked cells using the mouse or the keyboard. This setting is enabled by default.

• Select Unlocked Cells: If checked, the user can select unlocked cells using the mouse or the keyboard. This setting is enabled by default.

• Format Cells: If checked, the user can apply formatting to locked cells.

• Format Columns: If checked, the user can hide or change the width of columns.

• Format Rows: If checked, the user can hide or change the height of rows.

• Insert Columns: If checked, the user can insert new columns.

• Insert Rows: If checked, the user can insert new rows.

• Insert Hyperlinks: If checked, the user can insert hyperlinks (even in locked cells).

• Delete Columns: If checked, the user can delete columns.

• Delete Rows: If checked, the user can delete rows.

• Sort: If checked, the user can sort data in a range as long as the range doesn't contain any locked cells.

• Use AutoFilter: If checked, the user can use existing autofiltering.

• Use PivotTable Reports: If checked, the user can change the layout of pivot tables or create new pivot tables.

• Edit Objects: If checked, the user can make changes to objects (such as Shapes) and charts, as well as insert or delete comments.

• Edit Scenarios: If checked, the user can use scenarios (see Chapter 36).

When the worksheet is protected and the Select Unlocked Cells option is set, pressing Tab moves to the next unlocked cell, making data entry much easier.

31.2.3. Assigning user permissions

Excel also offers the ability to assign user-level permissions to different areas on a protected worksheet. You can specify which users can edit a particular range while the worksheet is protected. As an option, you can require a password to make changes.

This feature is rarely used, and the setup procedure is rather complicated. But if you need this level of protection, setting it up might be worth the effort.

Figure 31.4. The Allow Users to Edit Ranges dialog box.

31.3. Protecting a Workbook

Excel provides three ways to protect a workbook:

• Require a password to open the workbook.

• Prevent users from adding sheets, deleting sheets, hiding sheets, and unhiding sheets.

• Prevent users from changing the size or position of windows.

I discuss each of these methods in the sections that follow.

31.3.1. Requiring a password to open a workbook

Excel lets you save a workbook with a password. After doing so, whoever tries to open the workbook must enter the password.

To add a password to a workbook, follow these steps:

Figure 31.5. Specify a workbook password in the Encrypt Document dialog box.

To remove a password from a workbook, repeat the same procedure. In Step 2, however, delete the existing password symbols from the Encrypt Document dialog box, click OK, and save your workbook.

Figure 31.6 shows the Password dialog box that appears when you try to open a file saved with a password.

Figure 31.6. Opening this workbook requires a password.

Excel provides another way to add a password to a document:

31.3.2. Protecting a workbook's structure

To prevent others (or yourself) from performing certain actions in a workbook, you can protect the workbook's structure. When a workbook's structure is protected, the user may not

• Add a sheet.

• Delete a sheet.

• Hide a sheet.

• Unhide a sheet.

• Rename a sheet.

• Move a sheet.

To protect a worksheet's structure

To unprotect the workbook's structure, choose Review Changes Unprotect Workbook. If the workbook's structure was protected with a password, you are prompted to enter the password.

Figure 31.7. The Protect Workbook dialog box.

31.3.3. Protecting a workbook's windows

To prevent others (or yourself) from changing the size or position of a workbook's windows, you can protect the workbook's windows:

When a workbook's windows are protected, the user can't change anything related to the window size or position. For example, if the workbook window is maximized when the windows are protected, the user cannot unmaximize the window. The windows can, however, be zoomed.

To unprotect the workbook's windows, choose Review Changes Unprotect Workbook. If the workbook's windows were protected with a password, you are prompted to enter the password.

31.4. VB Project Protection

If your workbook contains any VBA macros, you may want to protect the VB Project to prevent others from viewing or modifying your macros. Another reason to protect a VB Project is to prevent its components from being expanded in the VB Editor Project window (which can avoid clutter while you're working on other VB project). To protect a VB Project

Figure 31.8. Protecting a VB Project with a password.

31.5. Related Topics

This section covers additional topics related to protecting and distributing your work.

31.5.1. Saving a worksheet as a PDF file

The PDF (Portable Document Format) file format is widely used as a way to present information in a read-only manner, with precise control over the layout. Software to display PDF files is available from a number of sources. Excel can create PDF files, but it cannot open them.

XPS is another "electronic paper" format, developed by Microsoft as an alternative to the PDF format. At this time, there is very little third-party support for the XPS format.

Save a worksheet in PDF or XPS format by choosing File Save and Send Create PDF/XPS Document Create a PDF/XPS. Excel displays its Publish as PDF Or XPS dialog box, in which you can specify a filename and location and set some other options.

31.5.2. Marking a workbook final

Excel lets you mark a document as "final." This action makes two changes to the workbook:

• It makes the workbook read-only so that the file can't be saved using the same name.

• It makes the workbook view-only so that nothing may be changed. When you open a finalized document, you see a message below the Ribbon. You can override its "final" status by clicking the Edit Anyway button.

To finalize a workbook, choose File Info Protect Workbook Mark as Final. Excel displays a dialog box in which you can confirm your choice.

Marking a document as final is not a security measure. Anyone who opens the workbook can cancel the mark as final designation. Therefore, this method doesn't guarantee that others will not change the workbook.

31.5.3. Inspecting a workbook

If you plan to distribute a workbook to others, you may want to have Excel check the file for hidden data and personal information. This tool can locate hidden information about you, your organization, or about the workbook. In some situations, you may not want to share this information with others.

To inspect a workbook, choose File Info Check for Issues Inspect Document. The Document Inspector dialog box opens, as shown in Figure 31.9. Click Inspect, and Excel displays the results of the inspection and gives you the opportunity to remove the items it finds.

If Excel identifies items in the Document Inspector, it doesn't necessarily mean that they should be removed. In other words, you should not blindly use the Remove All buttons to remove the items that Excel locates. For example, you may have a hidden sheet that serves a critical purpose. Excel will identify that hidden sheet and make it very easy for you to delete it. To be on the safe side, always make a backup copy of your workbook before running the Document Inspector.

31.5.4. Using a digital signature

Excel lets you add a digital signature to a workbook. Using a digital signature is somewhat analogous to signing a paper document. A digital signature helps to assure the authenticity of the workbook and also ensures that the content hasn't been modified since it was signed.

Figure 31.9. The Document Inspector dialog box identifies hidden and personal information in a workbook.

After you sign a workbook, the signature is valid until you make changes and resave the file.

31.5.4.1. Getting a digital ID

To digitally sign a workbook, you must obtain a certificate from a certified authority who is able to verify the authenticity of your signature. Prices vary, depending on the certificate granting company.

Another option is to create your own digital ID, but others will not be able to verify the authenticity. Creating your own digital ID is useful if you want to ensure that no one tampers with one of your signed workbooks.

31.5.4.2. Signing a workbook

Excel supports two types of digital signatures: a visible signature and an invisible signature.

To add a visible digital signature, choose Insert Text Signature Line Microsoft Office Signature Line. Excel displays its Signature Setup dialog box, and you're prompted for the information for the signature. After you add the signature box, double-click it to display the Sign dialog box, where you actually sign the document either by typing your name or uploading a scanned image of your signature.

Figure 31.10 shows a document with a visible digital signature.

Figure 31.10. This document has a digital signature.

To add an invisible digital signature, choose File Info Protect Add a Digital Signature. If the signed workbook is changed in any way, the digital signature is invalidated.