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.
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.
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.
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.
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 …
Well, that’s it. Hope you find this example useful. Thanks for reading.