The TextFrame Object in VBA with Examples

A TextFrame object is used to manipulate various elements of the text frame –basically a box of text — related to shape objects.

When we have a shape object (ex. rectangle, circle, etc.), we can control the text associated with the shape through the TextFrame object. This object includes one method and 15 properties.

The only method in the TextFrame object is the Characters method. It takes two optional parameters; start and length, given that we set the text property of the Characters method.

Name Required Data Type Description
Start Optional Variant Determines the starting character. If set to 1 or omitted then all of the text will be returned.
Length Optional Variant The number of characters to be returned.

The Characters method can have its text value set and the various text properties as well such as the font bold, font, etc. The following code sets the text of the characters method to “Hello World". Then, it sets the First letter (H) and the seventh letter (W) to bold.

'assume we already have a shape on the active sheet, and we set its text to Hello World.
ActiveSheet.Shapes(1).TextFrame.Characters.Text = “Hello World"
'bold the first letter (H)
ActiveSheet.Shapes(1).TextFrame.Characters(1,1).Font.Bold = True
'bold the 7th letter (W)
ActiveSheet.Shapes(1).TextFrame.Characters(7,1).Font.Bold = True

The result of the above code is shown as follows.

Textframe that says Hello World

TextFrame has many properties that can be manipulated to present it in a desirable format.

Property Description Value
Application The application that created the shape object Ex. “Microsoft Excel"
AutoMargins If True, then the values of the other margin properties (MarginBottom, MarginLeft, MarginRight, MarginTop) will be ignored True or False
AutoSize If True, then the size of the text frame on the shape will be adjusted to fit its text True or False
Creator The application that created the shape object (similar to the Application property) Ex. “XCEL"
HorizontalAlignment Sets the horizontal alignment value xlHAlignCenter, xlHAlignCenterAcrossSelection, xlHAlignDistributed, xlHAlignFill, xlHAlignGeneral, xlHAlignJustify, xlHAlignLeft, xlHAlignRight
VerticalAlignment Sets the vertical alignment value xlVAlignCenter, xlVAlignJustify, xlVAlignBottom, xlVAlignDistributed, xlVAlignTop
HorizontalOverflow Enables or disables horizontal overflow 0 (for no overflow), 1 ( for allowing overflow)
VerticalOverflow Enables or disables horizontal overflow 0 (for no overflow), 1 ( for allowing overflow)
MarginBottom, MarginLeft, MarginRight, MarginTop Sets the margin for the text frame. Integer value greater than 0.
Orientation Sets the value of the orientation. 3 (downward), 1(horizontal), 6 (horizontal and rotated as required for Asian language support), 2 (upward), 5 (vertical), 4 (vertical as required for Asian language support).
Parent Represents the parent of the textframe object Shape object
ReadingOrder Sets the reading order of the text. xlContext (according to context), xlLTR (left-to-right), xlRTL (right-to-left)

We start first by creating the shapes. The following code provides an example on how to create shapes.

Sub createShapes()
Set starShape = Sheets(1).Shapes.AddShape(msoShape10pointStar, 150, 20, 100, 30)
Set rectangleShape = Sheets(1).Shapes.AddShape(msoShapeRectangle, 406.8, 29.4, 67.8, 36)
End Sub

This results in the creation of a star shape and a rectangle shape that we set reference variables for.

Star and rectangle shapes

Example 1: Characters.Text

One of the most common and useful properties that TextFrame objects allow us to manipulate is the text that we can place in shapes. The below code sets the text of the shapes we just created to “Hello World!"

Sub CreateShapes()
Set starShape = Sheets(1).Shapes.AddShape(msoShape10pointStar, 150, 20, 100, 30)
Set rectangleShape = Sheets(1).Shapes.AddShape(msoShapeRectangle, 406.8, 29.4, 67.8, 36)
starShape.TextFrame.Characters.Text = "Hello World!"
rectangleShape.TextFrame.Characters.Text = "Hello World!"
End Sub
star and rectangle that say Hello World!

Example 2: Alignment

Here you can see the text being left-aligned in the rectangle and right-aligned in the star:

Sub createShapes_align()
Set starShape = Sheets(1).Shapes.AddShape(msoShape10pointStar, 150, 20, 100, 30)
Set rectangleShape = Sheets(1).Shapes.AddShape(msoShapeRectangle, 406.8, 29.4, 67.8, 36)
With rectangleShape.TextFrame
.Characters.Text = "Hello World!"
.Characters(1, 1).Font.Bold = True
.Characters(7, 1).Font.Bold = True
.HorizontalAlignment = xlHAlignLeft
End With
With starShape.TextFrame
.Characters.Text = "Hello World!"
.Characters(1, 1).Font.Bold = True
.Characters(7, 1).Font.Bold = True
.HorizontalAlignment = xlHAlignRight
End With
End Sub
Text is aligned inside the shape

Example 3: Margin

And finally, we can add margin around the characters too.

Sub createShapes_Margin()
Set rectangle2Shape = Sheets(1).Shapes.AddShape(msoShapeRectangle, 150, 20, 100, 30)
With rectangle1Shape.TextFrame
.Characters.Text = "Hello World"
.MarginLeft = 15
End With
With rectangle2Shape.TextFrame
.Characters.Text = "Hello World!"
.MarginLeft = 40
End With
End Sub
using margin with textarea

These are some examples that should help you gain insight into how to use the methods and properties of the TextFrame object. Whenever you’re using a shape in any of the office applications while programming in VBA, you’re likely to work with Textframe. These examples are a great starting point for getting introduced to this object.

Leave a Reply

Your email address will not be published. Required fields are marked *