8.4 Protection | Microsoft Office Automation with Visual FoxPro

Protection

When you re creating spreadsheets for other users to use, you don t want them to inadvertently delete formulae and data that you ve gone to great lengths to place into the worksheet. Excel has a number of ways to protect the data:

Require passwords to open, edit, close, and/or save a workbook;
Recommend that the workbook be opened as "read-only";
Hide a cell s formula, which leaves the cell visible but hides the formula on the formula bar;
Lock a workbook, worksheet, or cells, which leaves them visible but unchangeable.

To lock or hide cells, first set the cells Hidden and/or Locked properties as needed, and then turn on the Worksheet s protection. If you want to protect all cells on the worksheet, do not specify Hidden or Locked; just set the worksheet s protection.

Protecting a cell

All cells are locked and visible by default. However, locking a cell only matters when a worksheet is protected, and worksheets are not protected by default. Essentially, you need to unlock the cells that the user needs to change. The Range object has a Locked property that defaults to .T. To unlock a range, issue a command like this:

oSheet.Range("A1:F30").Locked = .F.

You hide a formula by using the FormulaHidden property (the formula s result is still displayed in the cell, but the formula itself is not displayed):

oSheet.Range("A1:F30").FormulaHidden = .T.

If the Worksheet is already locked, an error is generated stating that Excel is unable to set the Locked (or FormulaHidden) property. Be sure that you are protecting cells in an unlocked worksheet. The worksheet has several logical properties that you can check: ProtectContents and ProtectData are two properties that check for data; others check for protection on different kinds of objects.

Protecting a worksheet

Once you have the Locked and FormulaHidden properties set the way you want them, you can protect the worksheet with the Worksheet object s Protect method. It takes several parameters. The first is a case-sensitive character string to use as the worksheet s password. The remaining parameters are logical values pertaining to what is to be protected. In order, they are: drawing objects, contents (all the cells of a worksheet), scenarios (an analytical tool), and user interface (if true, it doesn t protect the macros).

To set the protection of just the worksheet s cells, pass .T. as the third parameter:

oSheet.Protect(,,.T.)

To protect the worksheet with a password, pass the password string as the first parameter:

oSheet.Protect("ThePassword", , .T.)

Warning: if you forget the password of a locked document, there is no way to determine the password (with Excel or Automation) to unlock the worksheet. You have to key in the exact, case-sensitive password, or you can t unlock the worksheet. Period. Use passwords at your own risk.

To unlock the worksheet, pass .F. as the third parameter. If a password was used, you must give the exact, case-sensitive string. If you do not pass a password string, Excel will prompt the user for the password; be sure your application can handle this wait state.

Protecting a workbook

Workbooks can also be protected. The structure of a workbook, meaning the position of the sheets within the workbook, can be protected, as can the windows of the workbook. In addition, the workbook can have its own password. The Workbook s Protect method takes three parameters: the first is the case-sensitive password string, the second is a logical indicator of whether to protect the structure of the workbook, and the third is a logical indicator of whether to protect the workbook s window positions. So either of these two syntaxes will work:

oBook.Protect("ThePassword", .T., .T.)

oBook.Protect(,.T., .T.)

Again, the warning: if you forget the password of a locked workbook, there is no way to determine the password (with Excel or Automation) to unlock the workbook. You have to key in the exact, case-sensitive password, or you can t unlock the workbook. Period. Use passwords at your own risk.

The procedure for unlocking a workbook is the same as that for unlocking a worksheet.