How to Add Text to Shapes Dynamically in Excel using VBA

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 properties. VBA’s “Shape” object provides a property called “TextFrame”, which gives access to the text of any shape. Let us see how you can use the VBA “TextFrame” property in your macro to add text to shapes dynamically.
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    If UCase(Sheet1.Cells(2, 1)) = "ERASER" Then
        Dim starShape As Shape     ' CREATE A SHAPE OBJECT.

        ' CREATE A 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

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.

Also Read: How Do We Add Hyperlinks in Excel using VBA macro?

To add a text to the shape, I am using the TextFrame property. However, 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 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 specified locations, horizontally.

Related: Change the Color of Shape in Excel Based on Cell Value using VBA

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

Well, that’s it. Hope you find this example useful. Thanks for reading.

Previous - Change the Color of Shape in Excel Based on Cell Value using VBANext - How to Extract or Copy Table data from Word to Excel using VBA



Like this Article? Subscribe now, and get all the latest articles and tips, right in your inbox.

Enter your email id

Delivered by FeedBurner
Tweet this article Google+

Related Posts:

Join our Google Plus Community and be a part of a discussion!