Page 183 - Microsoft Office Excel 2003 Programming Inside Out

Part 3: The Excel Object Model
Workbooks and Worksheets

● xlCalculationAutomatic, the default value, which causes Excel to recalculate your for-
mulas every time a change is made.
● xlCalculationManual, which requires a user to press Shift+F9 to recalculate the formu- Chapter 7
las in the active worksheet.
● xlCalculationSemiautomatic, which recalculates all formulas in a worksheet, except for
those formulas in a table.
If you only want to calculate formulas just before you save a workbook, you can set recalcu-
lation to manual and then set the Application object’s CalculateBeforeSave property to True,
as in the following code:

Application.Calculation = xlCalculationManual
Application.CalculateBeforeSave = True

Protect Method

The first line of defense against changes to a worksheet starts with requiring users to enter a
password before they are allowed to open the workbook. The Workbook object’s Protect
method is somewhat limited, allowing you to protect against unauthorized access to the
workbook, to protect against changes to the workbook’s structure, or to protect against
changes to the size and placement of the workbook’s windows. There’s quite a bit more going
on at the worksheet level, however, and the Sheet and Worksheet objects’ Protect methods
have correspondingly more options available.

expression.Protect(Password, DrawingObjects, Contents, Scenarios,
UserInterfaceOnly, AllowFormattingCells, AllowFormattingColumns,
AllowFormattingRows, AllowInsertingColumns, AllowInsertingRows,
AllowInsertingHyperlinks, AllowDeletingColumns, AllowDeletingRows,
AllowSorting, AllowFiltering, AllowUsingPivotTables)
Table 7-7 provides the default values for each of these parameters and describes what is or is
not allowed when the options are set to True or False.

Table 7-7. Parameters of the Worksheet.Protect Method
Parameter Default Description
Expression n/a@ A required expression that returns a Worksheet
object.
Password n/a@ A string that specifies a case-sensitive password
for the worksheet or workbook. If this argument is
omitted, you can unprotect the worksheet or
workbook without using a password.
DrawingObjects False When set to True, this parameter protects drawing
objects from changes.
continued

157