Page 176 - Microsoft Office Excel 2003 Programming Inside Out

Part 3: The Excel Object Model
Microsoft Office Excel 2003 Programming Inside Out

Visible Property
The other frequently used property in the Worksheets and Sheets collections is the Visible
property, which reflects whether a particular sheet is displayed on the sheet tab within a
workbook. There are three possible values for the Visible property: the xlSheetVisible,
Chapter 7
xlSheetHidden, and xlSheetVeryHidden Excel constants. When the Visible property is set to
xlSheetVisible, the sheet appears on the tab bar and can be edited directly by the user. When
the Visible property is set to xlSheetHidden, the sheet isn’t represented on the tab bar but the
user can display it by clicking Format, Sheet, Unhide and then clicking the name of the sheet
in the Unhide dialog box.

Setting the Visible property to xlSheetVeryHidden means that the hidden sheet doesn’t appear
in the Unhide dialog box and can only be made accessible by using VBA code to change the
Visible property to either xlSheetHidden or xlSheetVisible.

Methods

The Sheets collection is home to a wide variety of methods you can use to add, delete, move,
copy, and set the sheet’s page setup options (margins, headers, footers, and so on).

Add Method

Whenever you want to bring data in from an outside file, you should consider storing the
imported data in a new worksheet. To create a new worksheet in an existing workbook, you
use the Add method, which has the following syntax:

Sheets.Add(Before, After, Count, Type)
The Before and After parameters are mutually exclusive—which one you use depends on
where you want to place the new worksheet in the workbook. The Before and After parame-
ters can take an index value that reflects the position of the sheet in the workbook, a sheet
name, or the active sheet (using the ActiveSheet property as the value for the parameter).
For example, if you wanted to place the added worksheet at the front of the workbook, you
would set the Before parameter using any of the following statements:

150