Page 185 - Microsoft Office Excel 2003 Programming Inside Out

Part 3: The Excel Object Model
Workbooks and Worksheets

Another way you can extend a worksheet’s protection is to limit which cells the user can select
by assigning a value to the EnableSelection property. After you protect a worksheet, you can
set the EnableSelection property to any of the following Excel constants: Chapter 7
● xlNoSelection, which prevents the user from selecting any cells on the sheet
● xlUnlockedCells, which allows the user to select only those cells whose Locked property
is set to False
● xlNoRestrictions, the default value, which allows the user to select any value

The following code snippet prevents users from selecting any cells on the protected January
worksheet:

Worksheets("January").EnableSelection = xlNoSelection

Important Setting the EnableSelection property has an effect only if the worksheet is
protected.

As with a workbook, you can allow a user to unprotect a worksheet by providing the pass-
word. The line of code you use is simply this:

ActiveWorksheet. Unprotect
Excel displays an input box to accept the password for you, so you don’t even have to write
any additional code to handle the entry.

SaveAs Method

Just as you can save a workbook under a different name or to a different location, you can
save an individual worksheet as a separate file using the Worksheet method’s SaveAs method.

expression.SaveAs(FileName, FileFormat, Password, WriteResPassword,
ReadOnlyRecommended, CreateBackup, AddToMru, TextCodepage, TextVisualLayout,
Local)
The SaveAs method of the Worksheet object is very similar to the same method of the Work-
book object, so you can refer to Table 7-3 for details on most of the parameters of the Work-
sheet object’s SaveAs method.

Select Method

At first glance the Select method seems to be the same as the Activate method. The difference
between the two methods is that the Activate method only works on one worksheet at a time,
whereas the Select method lets you operate on more than one worksheet at a time. As an
example using the user interface, you can move two worksheets at a time by clicking the sheet
tab of the first worksheet, shift-clicking the second sheet tab, and dragging the sheets as a unit
to their new position in the workbook.

159