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
|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.|
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 has many properties that can be manipulated to present it in a desirable format.
|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.
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
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
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
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.