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.

NameRequiredData TypeDescription
StartOptionalVariantDetermines the starting character. If set to 1 or omitted then all of the text will be returned.
LengthOptionalVariantThe 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.

PropertyDescriptionValue
ApplicationThe application that created the shape objectEx. “Microsoft Excel”
AutoMarginsIf True, then the values of the other margin properties (MarginBottom, MarginLeft, MarginRight, MarginTop) will be ignoredTrue or False
AutoSizeIf True, then the size of the text frame on the shape will be adjusted to fit its textTrue or False
CreatorThe application that created the shape object (similar to the Application property)Ex. “XCEL”
HorizontalAlignmentSets the horizontal alignment valuexlHAlignCenter, xlHAlignCenterAcrossSelection, xlHAlignDistributed, xlHAlignFill, xlHAlignGeneral, xlHAlignJustify, xlHAlignLeft, xlHAlignRight
VerticalAlignmentSets the vertical alignment valuexlVAlignCenter, xlVAlignJustify, xlVAlignBottom, xlVAlignDistributed, xlVAlignTop
HorizontalOverflowEnables or disables horizontal overflow0 (for no overflow), 1 ( for allowing overflow)
VerticalOverflowEnables or disables horizontal overflow0 (for no overflow), 1 ( for allowing overflow)
MarginBottom, MarginLeft, MarginRight, MarginTopSets the margin for the text frame.Integer value greater than 0.
OrientationSets 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).
ParentRepresents the parent of the textframe objectShape object
ReadingOrderSets 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

Contents

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 *