How to Add Text to Shapes Dynamically in Excel using VBA

It is very easy to add text to shapes dynamically in Excel using VBA, provided you know the right methods and properties. VBA’s Shape object provides a property called TextFrame, which gives access to the text of a shape. I'll show you how to use the VBA "TextFrame" property in your code to add text to shapes dynamically.

Here's an example. Also see the image below.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If UCase(Sheet1.Cells(2, 1)) = "ERASER" Then
        ' CREATE A SHAPE.
        Dim starShape As Shape
        Set starShape = Sheet1.Shapes.AddShape(msoShape10pointStar, 150, 20, 100, 30)
        
        With starShape
            .ShapeStyle = msoLineStylePreset7
            .TextFrame.Characters.Text = "In Stock"    ' ADD TEXT TO THE SHAPE.
        End With
    End If
End Sub

dynamically add text to a shape in excel using vba

The above code works when you enter some value in the cell A2 or Cells(2,1). If the value is eraser, it creates a shape (a Point Star shape) dynamically at a specified location. I have specified the shapes properties such as the location "left" and "top", along with the "width" and "height".

I am using the TextFrame property to add text to the shape. But, you cannot simply assign a value as text to the property. You have to use the Characters function (a member of TextFrame) that actually has the text property.

You can choose a particular type of shape or different types of shapes from a list of "pre-defined" shapes.

Different types of Shapes to Choose in Excel using VBA

In-addition, I am using the ShapeStyle property to define a style for the shape. In-fact you can choose a style from an array of styles.

Different Styles for Shapes in Excel using VBA

So, now you know how to add shapes and add text to the shapes dynamically in Excel using VBA. It’s a simple method and you can format any number of shapes quickly using a small macro.

Here’s another example. I am sure you will like it too. It creates multiple shapes at a specified duration, and at specified locations, horizontally.

First, add a button (an ActiveX control) on the worksheet and write the macro in the button’s click event.

Option Explicit

Private Sub CommandButton1_Click()
    ' DYNAMICALLY ADD MULTIPLE SHAPES IN EXCEL BASED ON A CONDITION.

    Dim i, j, k, iLeft As Integer
    j = 400
    k = 1
    iLeft = 10
    
    For i = 1 To 500
        
        Range("A3").Cells(2, k) = i  ' SHOW THE VALUE.

        If (i = (500 - j)) Then
                    
            Dim ovalShape As Shape
            Set ovalShape = Sheet1.Shapes.AddShape(msoShapeOvalCallout, iLeft, 15, 70, 20)
            
            With ovalShape
                ovalShape.ShapeStyle = msoLineStylePreset7
                ovalShape.TextFrame.Characters.Text = "at " & i   ' ADD TEXT TO THE SHAPES.
            End With

            j = j - 100
            k = k + 1
            iLeft = iLeft + 70
        End If
        
        DoEvents
    Next
End Sub

The output would be …

Dynamically Add Multiple Shapes and Texts to the Shape in Excel using VBA

← PreviousNext →