Sheet (object)

<< Click to Display Table of Contents >>

Sheet (object)

Access paths:

Application à Workbooks à Item à Sheets à Item

Application à Workbooks à ActiveSheet

Application à ActiveWorkbook à ActiveSheet

Application à ActiveSheet

 1  Description

A Sheet object represents an individual worksheet of a document opened in PlanMaker.

An individual Sheet object exists for each worksheet. If you add worksheets to the document or delete them, the respective Sheet objects will be created or deleted dynamically.

 2  Access to the object

The individual Sheet objects can be accessed in the following ways:

All worksheets of a document are administrated in the Workbook.Sheets collection (type: Sheets):

' Display the names of all worksheets in the active document

For i = 1 To pm.Application.ActiveWorkbook.Sheets.Count

 MsgBox pm.Application.ActiveWorkbook.Sheets.Item(i).Name

Next i

The active worksheet of a document can be retrieved from the Workbook.ActiveSheet object:

' Display the name of the active worksheet

MsgBox pm.Application.Workbooks(1).ActiveSheet.Name

The active worksheet of the active document can be retrieved from the Application.ActiveSheet object:

' Display the name of the active worksheet in the active document

MsgBox pm.Application.ActiveSheet.Name

Sheet is the Parent object for several objects that are linked to it, for example, Range or AutoFilter:

' Show the name of the current worksheet in an indirect way

MsgBox pm.Application.ActiveSheet.Range("A1:B20").Parent.Name

 3  Properties, objects, collections and methods

Properties:

Name (default property)

Index R/O

Hidden

PageBreaks

DisplayRowHeadings

DisplayColumnHeadings

AutoFilterMode

 

Objects:

PageSetup PageSetup

Selection Range

Rows Rows

Columns Columns

Cells Range

Range Range

AutoFilter AutoFilter

Application Application

Parent Sheets

 

Methods:

Activate

Calculate

Delete

Move

Select

ShowAllData

Name (property)

Data type: String

Gets or sets the name of the worksheet.

Index (property, R/O)

Data type: Long

Returns the numeric index of the worksheet within the other worksheets (see also Move).

Hidden (property)

Data type: Boolean

Gets or sets the setting whether the worksheet is hidden. Corresponds to the ribbon commands Insert | Tables group | Sheet | Show and Hide in PlanMaker.

PageBreaks (property)

Data type: Boolean

Gets or sets the setting whether page breaks should be displayed in the worksheet. Corresponds to the setting Page breaks in the dialog box of the ribbon command Insert | Tables group | Sheet | Properties in PlanMaker.

DisplayRowHeadings (property)

Data type: Boolean

Gets or sets the setting whether row headings should be shown in the worksheet. Corresponds to the setting Row headers in the dialog box of the ribbon command Insert | Tables group | Sheet | Properties.

DisplayColumnHeadings (property)

Data type: Boolean

Gets or sets the setting whether column headings should be shown in the worksheet. Corresponds to the setting Column headers in the dialog box of the ribbon command Insert | Tables group | Sheet | Properties.

DisplayGridlines (property)

Data type: Boolean

Gets or sets the setting whether grid lines should be shown in the worksheet. Corresponds to the setting Gridlines in the dialog box of the ribbon command Insert | Tables group | Sheet | Properties.

GridlineColor (property)

Data type: Long (SmoColor)

Gets or sets the color of the grid lines as a "BGR" value (Blue-Green-Red triplet). You can either indicate an arbitrary value or use one of the pre-defined BGR color constants.

GridlineColorIndex (property)

Data type: Long (SmoColorIndex)

Gets or sets the color of the grid lines as an index color. "Index colors" are the standard colors of PlanMaker, consecutively numbered from -1 for transparent to 15 for light gray. You may use the values shown in the Index colors table.

Note: It is recommended to use the GridlineColor property (see above) instead of this one, since it is not limited to the standard colors but enables you to access the entire BGR color palette.

AutoFilterMode (property)

Gets or sets the setting whether drop-down arrows should be shown for the active AutoFilter.

Note: You can always read this setting. But if you want to set it, you should note that this property can only be used to hide the drop-down arrows. To show the drop-down arrows, you must invoke the AutoFilter method from the Range object instead.

PageSetup (pointer to object)

Data type: Object

Returns the PageSetup object that you can use to access the page formatting of the worksheet (paper format, margins, etc.).

Selection (pointer to object)

Data type: Object

Returns a Range object that represents the currently selected cells of the worksheet. Among other things, you can use it to read and change their contents and formatting.

If nothing is selected in the worksheet, the Range object represents the current cell.

Rows (pointer to object)

Data type: Object

Returns the Rows collection, a collection of all rows in the worksheet.

The individual elements of this collection are Range objects. You can therefore apply all properties and methods of ranges to them.

Example:

' Set the font for all cells in row 10 to Courier New

pm.ActiveSheet.Rows(10).Font.Name = "Courier New"

Columns (pointer to object)

Data type: Object

Returns the Columns collection, a collection of all rows in the worksheet.

The individual elements of this collection are Range objects. You can therefore apply all properties and methods of ranges to them.

Example:

' Set the font for all cells in column C (= 3rd column) to Courier New

pm.ActiveSheet.Columns(3).Font.Name = "Courier New"

Cells (pointer to object)

Data type: Object

Returns a Range object that contains all cells of the current worksheet. This is useful for two applications:

You can apply an operation (primarily formatting) to each cell in the worksheet:

' Make the whole worksheet red

pm.ActiveSheet.Cells.Shading.ForegroundPatternColor = smoColorRed

You can address the individual cells using loop variables instead of manually building a string with the cell address (for example, "B5" for the second column in the fifth row). To do this, use the Item property of the Range object returned by the Cells pointer, for example:

' Fill the first 5 by 10 cells of the active worksheet

Dim row, col as Integer

For row = 1 To 5

 For col = 1 to 10

                 pm.ActiveSheet.Cells.Item(row, col).Value = 42

 Next col

Next row

Range (pointer to object)

Data type: Object

Returns a Range object matching the specified parameters. You can use this object to access the cells in a range and, for example, get or set their values.

Syntax 1:

 obj = Range(Cell1)

Syntax 2:

 obj = Range(Cell1, Cell2)

Parameters:

Cell1 (type: String) specifies either according to syntax 1 a cell range (then Cell2 must be omitted) or according to syntax 2 the upper left corner of a range (then parameter Cell2 specifies the lower right corner of the range).
Cell2 (optional; type: String) should be used only if Cell1 refers to an individual cell.
Examples for syntax 1:

 Range("A1:B20")                ' Cells A1 to B20

 Range("A1")                ' Only cell A1

 Range("A:A")                ' The whole column A

 Range("3:3")                ' The whole row 3

 Range("Summer")                ' Named range "Summer"

Example for syntax 2:

 Range("A1", "B20")        ' Cells A1 to B20

Example:

' Select the cells from A1 to B20 in the active worksheet

pm.ActiveSheet.Range("A1:B20").Select

AutoFilter (pointer to object)

Data type: Object

Returns the AutoFilter object that lets you access the AutoFilter of the worksheet.

Application (pointer to object)

Returns the Application object.

Parent (pointer to object)

Returns the parent object, i.e. Sheets.

Activate (method)

Makes the worksheet become the active worksheet.

Syntax:

 Activate

Parameters:

none

Return type:

none

Example:

' Bring the first sheet of the active document to the front

pm.ActiveWorkbook.Sheets(1).Activate

Calculate (method)

Recalculates the worksheet (similar to the ribbon command Formulas | Update group | Update data | Update calculations in PlanMaker, except that the ribbon command recalculates all worksheets of a workbook).

Syntax:

 Calculate

Parameters:

none

Return type:

none

Example:

' Recalculate the first worksheet

pm.ActiveWorkbook.Sheets(1).Calculate

Delete (method)

Deletes the worksheet from the document.

Syntax:

 Delete

Parameters:

none

Return type:

none

Example:

' Delete the first sheet from the active document

pm.ActiveWorkbook.Sheets(1).Delete

Move (method)

Changes the position of the worksheet within the other worksheets.

Syntax:

 Move Index

Parameters:

Index (type: Long) indicates the target position.

Return type:

none

Example:

' Move the active worksheet to the third position

pm.ActiveSheet.Move 3

Select (method)

Selects all cells of the worksheet (corresponds to the ribbon command Home | Selection group | Select all in PlanMaker).

Syntax:

 Select

Parameters:

none

Return type:

none

Example:

' Select all cells in the current worksheet

pm.ActiveSheet.Select

ShowAllData (method)

Makes all cells visible again that are currently hidden by an AutoFilter. Corresponds to clicking the entry "(All)" in the drop-down menu that appears when you click on the arrow button of an AutoFilter.