Читать книгу Excel 2019 Power Programming with VBA - Michael Alexander, Dick Kusleika - Страница 51

If you think about it, destroying a spreadsheet is fairly easy. Erasing one critical formula or value can cause errors throughout the entire worksheet—and perhaps even other dependent worksheets. Even worse, if the damaged workbook is saved, it replaces the good copy on disk. Unless a backup procedure is in place, the user of your application may be in trouble, and you will probably be blamed for it.

Obviously, you can easily see why you need to add some protection when users—especially novices—will be using your worksheets. Excel provides several techniques for protecting worksheets and parts of worksheets.

 Lock specific cells You can lock specific cells (by using the Protection tab in the Format Cells dialog box) so that users can't change them. Locking takes effect only when the document is protected with the Review ➪ Changes ➪ Protect Sheet command. The Protect Sheet dialog box has options that allow you to specify which actions users can perform on a protected sheet (see Figure 1.5).FIGURE 1.5 Using the Protect Sheet dialog box to specify what users can and can't do

 Hide the formulas in specific cells You can hide the formulas in specific cells (by using the Protection tab in the Format Cells dialog box) so that others can't see them. Again, hiding takes effect only when the document is protected by choosing the Review ➪ Changes ➪ Protect Sheet command.

 Protect an entire workbook You can protect an entire workbook—the structure of the workbook, the window position and size, or both. Use the Review ➪ Protect ➪ Protect Workbook command for this purpose.

 Lock objects on the worksheet Use the Properties section in the task pane to lock objects (such as shapes) and prevent them from being moved or changed. To access this section of the task pane, right-click the object and choose Size and Properties. Locking objects takes effect only when the document is protected using the Review ➪ Protect ➪ Protect Sheet command. By default, all objects are locked.

 Hide rows, columns, sheets, and documents You can hide rows, columns, sheets, and entire workbooks. Doing so helps prevent the worksheet from looking cluttered, and it also provides some modest protection against prying eyes.

 Designate an Excel workbook as read-only recommended You can designate an Excel workbook as read-only recommended (and use a password) to ensure that the file can't be overwritten with any changes. You make this designation in the General Options dialog box. Display this dialog box by choosing File ➪ Save As, choosing a directory, and then clicking the Tools button found on the Save As dialog box. Choose General Options to specify the appropriate password.

 Assign a password You can assign a password to prevent unauthorized users from opening your file. Choose File ➪ Info ➪ Protect Workbook ➪ Encrypt with Password.

 Use a password-protected add-in You can use a password-protected add-in, which doesn't allow the user to change anything on their worksheets.