[Excel VBA] How to Add Callout Shapes to a Worksheet (Beginner’s Guide)

Introduction

When creating documents in Excel, do you ever feel the need to highlight specific cell contents or automatically add annotations to a report? Adding shapes manually can be time-consuming, but with VBA macros, you can instantly add callouts with specific formats at designated positions.

In this article, I will explain how to use VBA to add a “Rounded Rectangular Callout” to a worksheet and configure its format and text. This guide is designed to be easy for beginners to understand.

Completed Code

Below is the complete VBA code. Copy and paste this into a standard module in the Visual Basic Editor (VBE) and run it. It will create a callout shape over the range D3:F7.

Sub AddCalloutShape_Sample()

    ' Declare variables
    Dim newCallout As Shape
    Dim targetRange As Range
    
    ' Set the reference cell range where the callout will be placed
    Set targetRange = ActiveSheet.Range("D3:F7")
    
    ' Add a rounded rectangular callout shape at the specified position and size
    ' The AddShape method requires: Type, Left, Top, Width, Height
    Set newCallout = ActiveSheet.Shapes.AddShape( _
        Type:=msoShapeRoundedRectangularCallout, _
        Left:=targetRange.Left, _
        Top:=targetRange.Top, _
        Width:=targetRange.Width, _
        Height:=targetRange.Height)
        
    ' Configure the format of the added shape
    With newCallout
        ' Set the name of the shape
        .Name = "CalloutCreatedByVBA"
        
        ' Adjust the position of the callout pointer (1st adjustment handle)
        .Adjustments(1) = -0.5
        
        ' Adjust the base of the pointer (2nd adjustment handle)
        .Adjustments(2) = 0.3
        
        ' Set the fill color to Teal Green using RGB
        .Fill.ForeColor.RGB = RGB(0, 128, 128)
        
        ' Set the text inside the shape
        .TextFrame.Characters.Text = "Enter your message here"
        
        ' Set the text color to White
        .TextFrame.Characters.Font.Color = RGB(255, 255, 255)
        
    End With

    ' Release object variables
    Set targetRange = Nothing
    Set newCallout = Nothing

End Sub

Detailed Explanation

Let’s look at the code step by step.

1. Declaring Variables

Dim newCallout As Shape
Dim targetRange As Range

First, we declare two variables:

• newCallout: A variable to store the created shape object. We use the Shape type.
• targetRange: A variable to store the cell range that serves as the base for the shape’s position. We use the Range type.

2. Setting the Reference Range

Set targetRange = ActiveSheet.Range("D3:F7")

We assign the range “D3:F7” to the targetRange variable using the Set keyword. We will use the properties (Left, Top, Width, Height) of this range later when creating the shape.

3. Adding the Shape (AddShape Method)

Set newCallout = ActiveSheet.Shapes.AddShape( _
    Type:=msoShapeRoundedRectangularCallout, _
    Left:=targetRange.Left, _
    Top:=targetRange.Top, _
    Width:=targetRange.Width, _
    Height:=targetRange.Height)

This is the core part of the code. We use the ActiveSheet.Shapes.AddShape method to create a new shape and use Set to store it in the newCallout variable.

• Type: Specifies the type of shape. msoShapeRoundedRectangularCallout creates a rounded rectangular callout. You can use other types like msoShapeRectangle (Rectangle).
• Left, Top: Specifies the position of the top-left corner. We use the .Left and .Top properties of our targetRange.
• Width, Height: Specifies the size. We use the .Width and .Height of our targetRange.

4. Formatting the Shape (With Statement)

With newCallout
    .Name = "CalloutCreatedByVBA"
    .Adjustments(1) = -0.5
    .Adjustments(2) = 0.3
    .Fill.ForeColor.RGB = RGB(0, 128, 128)
    .TextFrame.Characters.Text = "Enter your message here"
    .TextFrame.Characters.Font.Color = RGB(255, 255, 255)
End With

Using the With newCallout ... End With block allows us to set multiple properties cleanly.

• Name: Gives the shape a specific name. This is useful if you need to manipulate this specific shape later.
• Adjustments: Adjusts the yellow diamond handles you see when selecting a shape. For callouts, this changes the position and angle of the pointer (the tail).
• Fill.ForeColor.RGB: Sets the fill color. RGB(0, 128, 128) creates a deep teal green.
• TextFrame.Characters.Text: Sets the text string inside the shape.
• TextFrame.Characters.Font.Color: Sets the font color. We used white (RGB(255, 255, 255)) to contrast with the dark background.

Customization Tips

You can create various shapes by modifying this code.

• Change the Shape Type: Change the Type argument. For example, msoShapeOval creates an oval, and msoShapeFlowchartTerminator creates a flowchart terminator symbol.
• Change the Color: Change the numbers in the RGB function to find your preferred color.
• Change the Location: Simply change Range("D3:F7") to any other range to automatically adjust the position and size.

Summary

In this article, I explained how to use Excel VBA to add a callout shape to a worksheet and configure its format.

Key Takeaways:

• Use the Shapes.AddShape method to add shapes.
• Use a Range object to easily manage position and size.
• Customize the shape by modifying the properties (Color, Text, Adjustments) of the Shape object.

Automating shape creation with VBA is very useful for standardized reports and visually emphasizing data analysis results. Please try customizing it to fit your needs.