Borders (collection)

<< Click to Display Table of Contents >>

Borders (collection)

Access paths:

Application à Workbooks à Item à Sheets à Item à Range à Borders

Application à Workbooks à ActiveSheet à Range à Borders

Application à ActiveWorkbook à ActiveSheet à Range à Borders

Application à ActiveSheet à Range à Borders

Instead of Range, you can also use other objects and properties that return a Range object: ActiveCell, Selection, Rows(n), Columns(n) and Cells(x, y). You can find examples of these access paths in the Range-Object.

 1  Description

The Borders collection represents the four border lines of cells (left, right, top and bottom). You can use this collection to get or change the line settings (thickness, color, etc.) of each border line.

The individual elements of the Borders collection are of the type Border.

The parameter you pass to the Borders collection is the number of the border line you want to access:

pmBorderTop        = -1 ' Top border line

pmBorderLeft       = -2 ' Left border line

pmBorderBottom     = -3 ' Bottom border line

pmBorderRight      = -4 ' Right border line

pmBorderHorizontal = -5 ' Horizontal grid lines

pmBorderVertical   = -6 ' Vertical grid lines

Example:

' Set the color of the left line of cell A1 to red

pm.ActiveSheet.Range("A1").Borders(pmBorderLeft).Color = smoColorRed

 2  Access to the object

As a child object of a Range object, Borders represents the border lines of the cells in the given range, corresponding to the ribbon command Home | Format group | Borders.

Example:

' Draw a bottom border for the cell A1

pm.ActiveSheet.Range("A1").Borders(pmBorderBottom).Type = pmLineStyleSingle

 3  Properties, objects, collections and methods

Properties:

Count R/O

 

Objects:

Item Border (default object)

Application Application

Parent Range

Count (property, R/O)

Data type: Long

Returns the number of Border objects in the collection, i.e. the number of possible border lines: This value is always 4 because there are four borders (left, right, top and bottom).

Item (pointer to object)

Data type: Object

Returns an individual Border object that you can use to get or set the properties (such as color and thickness) of one individual border line.

Which Border object you get depends on the numeric value that you pass to Item. The following table shows the admissible values:

pmBorderTop        = -1 ' Top border line

pmBorderLeft       = -2 ' Left border line

pmBorderBottom     = -3 ' Bottom border line

pmBorderRight      = -4 ' Right border line

pmBorderHorizontal = -5 ' Horizontal grid lines

pmBorderVertical   = -6 ' Vertical grid lines

Application (pointer to object)

Data type: Object

Returns the Application object.

Parent (pointer to object)

Data type: Object

Returns the parent object, i.e. an object of the type Range.

Example for the Borders object

In the following example, a 4 point thick blue line will be applied to the left border of the range from B2 to D4. Then, a thin red double line will be applied to the right border.

Sub Main

 Dim pm as Object

 

 Set pm = CreateObject("PlanMaker.Application")

 pm.Visible = True

 

 With pm.ActiveSheet.Range("B2:D4")

         .Borders(pmBorderLeft).Type    = pmLineStyleSingle

         .Borders(pmBorderLeft).Thick1  = 4

         .Borders(pmBorderLeft).Color   = pmColorBlue

         .Borders(pmBorderRight).Type   = pmLineStyleDouble

         .Borders(pmBorderRight).Thick1 = 1

         .Borders(pmBorderRight).Thick2 = 1

         .Borders(pmBorderRight).Color  = smoColorRed

 End With

 

 Set pm = Nothing

End Sub