[Excel VBA] How to Add and Format Text in Shapes

Introduction

Excel Shapes are not just drawings; by adding text to them, they can serve as explanatory callouts, buttons, or clear labels. Automating this text addition and formatting with VBA can dramatically improve the efficiency of creating reports and manuals.

In this article, I will explain how to use VBA to insert text into a shape and freely configure its alignment, font size, and color.

The Basics of Text Manipulation: The TextFrame Object

Operations related to text inside a shape are primarily done through the TextFrame object of the Shape object. By manipulating the properties of this TextFrame object, you can control various text formats using VBA.

Practical Code: Adding and Formatting Text in a Shape

Preparation

First, to manipulate it with VBA, create a shape on your worksheet and name it “InfoBox“. (How to name: Select the shape → “Shape Format” tab → “Selection Pane” → Change the name)

VBA Code Example

The following code adds text to the shape named “InfoBox” and sets its formatting (alignment, font size, and color).

Sub AddAndFormatTextInShape()

    ' Target the shape named "InfoBox"
    ' Use the With statement to group settings for TextFrame
    With ActiveSheet.Shapes("InfoBox").TextFrame
    
        ' Set vertical alignment to "Center"
        .VerticalAlignment = xlVAlignCenter
        
        ' Set horizontal alignment to "Center"
        .HorizontalAlignment = xlHAlignCenter
        
        ' Set the text string to display
        .Characters.Text = "Text via VBA"
        
        ' Further manipulate the Font property of the Characters object
        With .Characters.Font
            ' Set font size to 18 points
            .Size = 18
            ' Set font color to White
            .Color = RGB(255, 255, 255)
            ' Make it Bold
            .Bold = True
        End With
        
    End With

End Sub

Code Explanation

• With ActiveSheet.Shapes("InfoBox").TextFrame Using the With statement allows you to cleanly write multiple property settings for the TextFrame object.
• .VerticalAlignment and .HorizontalAlignment These specify the text alignment. Besides xlVAlignCenter (Vertical Center) and xlHAlignCenter (Horizontal Center), you can use constants like xlVAlignTop (Top) or xlHAlignLeft (Left).
• .Characters.Text This assigns the actual string you want to display inside the shape. The .Characters object handles settings related to the text itself and its character collection.
• With .Characters.Font By manipulating the Font object belonging to .Characters, you can configure detailed font settings. You can set the size with .Size, color with .Color (using the RGB function), and boldness with .Bold (True/False).

Note: Advanced Formatting with TextFrame2

VBA also has an object called TextFrame2, which is very similar to TextFrame.

• TextFrame: The traditional object. Simple and easy to understand, used for applying the same formatting to the entire text.
• TextFrame2: A newer object. It allows for more advanced and complex formatting, such as changing the color or boldness of only specific parts of the text.

In most cases, TextFrame is sufficient. However, if you have special requirements, such as making only the word “VBA” red, you will need TextFrame2.

Sub SetTextWithTextFrame2()
    ' Text settings using TextFrame2
    Dim txtRange As TextRange2
    Set txtRange = ActiveSheet.Shapes("InfoBox").TextFrame2.TextRange

    txtRange.Text = "Advanced Text Settings"

    ' For example, make only the first 8 characters red
    txtRange.Characters(1, 8).Font.Fill.ForeColor.RGB = RGB(255, 0, 0)
End Sub

TextFrame2 is more powerful but has a slightly deeper hierarchy. It is recommended to master TextFrame first and then look into TextFrame2 when necessary.

Summary

In this article, I explained how to set and format text in shapes using VBA.

• Text manipulation in shapes is based on the .TextFrame object.
• Use .VerticalAlignment / .HorizontalAlignment to determine text position.
• Use .Characters.Text to set the string and .Characters.Font to set the font style.
• Use TextFrame for simple global settings, and TextFrame2 for complex, character-specific settings.

By using this technique, you can automatically add annotations to reports generated by macros or display user guides, greatly enhancing the expressiveness of your VBA applications.