<< 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") ' Range labeled "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.