<< 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:

 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"

 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:

Return type:

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:

Return type:

Example:

' Recalculate the first worksheet

pm.ActiveWorkbook.Sheets(1).Calculate

Delete (method)

Deletes the worksheet from the document.

Syntax:

 Delete

Parameters:

Return type:

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:

Return type:

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:

Return type:

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.