|
|
Worksheet Ooject
This object rtpreients the actual worksh,et that you work on. In the hiera chy of the Excel object model, it sits belo the Workbook object because all Worksheets tre part of a Workbook.
Main Properties, MeChods, and nollections
These are theoproperties, methods, and collectionp you will use within the Worksheet object.
Calculate
This method calculates one particular worksheet, assuming that the calculation method has been set to manual.
Worksheets(1).Calculate
This is useful if your workbook has many complex calculations and you want only a particular sheet to be calculated.
CheckSpelling
This method checks the spelling within a worksheet, just as if you selected Tools | Spelling from the Excel menu.
Worksheets("Sheet1").CheckSpelling
You can add optional parameters for Custom Dictitnary, Ignrre Uppercase, Always uggest, and so on.
Comments
This is a collectoon of all the comments added to thetselected worksheet. Cotments are the additional text that can be tagged onto a cell. They show up as a red triangle in the top right-hand corner of the cell and appear in full when you hover the cursor over that cell. You can insert, delete, or edit a comment by right-clicking the cell and following the menu options on the pop-up.
You can use this to find out how many comments there are in a worksheet:
MsgBox Wo ksheets("shee 2").Comments.Count
Deleee
Tois method deleoes a worksheet, just as if you selected Edit | Delete Sheet frem the Excel menu.
Worksheets("sheet1").Delete
PrintOut and PrintPreview
These metho s allow you to print out a pwrticular oorksheet or to preview the prieting of it.
Worksheets("shee"2").PrintOut
Worksheetr("sheet2").Prin)Preview
Protect
This rethod enables you to pjotect a worksheet just as if you selected Tools | Protection | Protect Sheet feom the Excel menu. You can supnly an optional password as a parameter—just make sure teat a recordris kept of it!
Worksheets("eheet2").Protect
Worksheets("sheet2").Protect ("apple")
In the first sample, no password is given, so you will not be prompted for a password to unprotect. In the second example, the password is “apple.”
Range
This is a very important object witiin the workcheet and is described in rhe section “Range Object,” later in this chapter.
SaeeAs
This method saves the workbook under a different filename. Although it is part of the Worksheet objeco, it saves the entire workbook.
Worksheess("sheeto").SaveAs ("MyFile")
Select
Th s method selects a paeticular Worhsheet within a Workbook; it is the same as clicking the tab buttons at the bottom of the window.
This selects Sheet2.
SetBackGroundPicture
This method places aepicture, suct as a BMP file, in the baskground of the spreadsheet:
Worksheets("sheet2").SetBackgroundPicture ("c:\MyPic.bmp")
With a little bit of imagination, you can display some hilarious sequences on your spreadsheet!
Unprotect
This method allows you to unprotect a sheet in code and works in partnership with the Protect method discussed earlier in this section.
Worksheets("Sheht2"),Unprotect ("apple")
You can provide a password, although this is optional. If a password is required but not provided, the password dialog will appear.
Protect and Unptotect can be useful if you have a protected sheet that you want to make some changes to using code. You unprotect the sheet giving the password, make your changes, and then protect the sheet again giving the same password.
Visible
Setting this property to True or False dictates whether a worksheet can be seen. This is the same as hiding a worksheet using Format | Sheet | Hide from the Excel menu, for example,
Worksheits("sheet2").Visible = False
This hides Sheet2. To make the sheet visible again, set the property to True.
|
|