VBA Coding For Shapes (The Complete Guide)
VBA Coding With Shape Objects
In this comprehensive guide, you will be learning all the ways you can create and manipulate shapes with VBA macros.
Shapes are objects you can insert into your spreadsheet through the Insert Tab via the Shapes gallery button. These objects can add visualizations to your dashboards, store text, or even serve as buttons to launch macro code.
Creating A New Shape With AddShape()
To create a shape object in Excel using VBA, you must call the AddShape function.
The AddShape function has 4 required inputs in order to generate a new shape:
- Type – Name of the type of shape you wish to generate
- Left – Where on the spreadsheet the left side of the shape should be located
- Top – Where on the spreadsheet the top of the shape should be located
- Width – How wide your shape should be
- Height – How tall your shape should be
Here is a snippet of VBA code showing how to create 2 shapes and store the newly created shape to a variable for easy reference later on in your code.
Sub CreateShape()
Dim shp1 As Shape
Dim shp2 As Shape
'Create & Store New Shape to Variable
Set shp1 = ActiveSheet.Shapes.AddShape(msoShape16pointStar, _
ActiveCell.Left, ActiveCell.Top, 80, 27)
'Create & Store New Shape to Variable (use Enum Code)
Set shp2 = ActiveSheet.Shapes.AddShape(94, _
ActiveCell.Left, ActiveCell.Top, 80, 27)
End SubContinue reading through the next few sections to learn how to determine the type, size, and position values you should be using to create your desired shape.
Choosing A Shape Type
There are a TON of shape types available to you through VBA. There are so many in fact, that I have painstakingly gone through and visually cataloged them for your ease in the below slide show.
Once you have determined which shape you would like to create, grab either the shape textual name or the enumeration number. You will use this MSOAutoShapeType reference to code the exact shape you want.
If you have a shape already created on your spreadsheet, you can use the following code to figure out its enumeration code that you can reference in your VBA code.
Sub DetermineShapeType()
'PURPOSE: Display The Shape Type of Selected Shape
'SOURCE: www.TheSpreadsheetGuru.com
Dim ActiveShape As Shape
Dim UserSelection As Variant
'Pull-in what is selected on screen
Set UserSelection = ActiveWindow.Selection
'Determine if selection is a shape
On Error GoTo NoShapeSelected
Set ActiveShape = ActiveSheet.Shapes(UserSelection.Name)
On Error Resume Next
'Tell User the Shape Type Enumeration Number
MsgBox "The Select Shape Type = " & ActiveShape.AutoShapeType
Exit Sub
'Error Handler
NoShapeSelected:
MsgBox "You do not have a shape selected!"
End SubDetermining Shape Position
There are two properties you can modify to change the location of a shape on the spreadsheet with VBA. These two properties are the Left and Top values of the shape.
If you are unsure what the size of your shape should be, there are two popular ways you can size your shape:
Method 1: You can base it on the left and top positions of a cell on your spreadsheet.
The following VBA code shows you how to use the Left value of Cell B1 and the Top value of Cell B10 to reposition the rectangle shape that is created.
Sub ShapePositionFromCell()
Dim shp As Shape
'Create Shape
Set shp = ActiveSheet.Shapes.AddShape(msoShapeRectangle, _
Range("B1").Left, Range("B10").Top, 100, 50)
End SubMethod 2: You can position the shape to your liking manually on the spreadsheet and read the left and top positions using VBA.
The following VBA code will output a message box that displays the Left and Top positions of a currently selected shape (ActiveShape).
Sub DetermineShapePosition()
'PURPOSE: Display Selected Shape's Position
'SOURCE: www.TheSpreadsheetGuru.com
Dim ActiveShape As Shape
Dim UserSelection As Variant
'Pull-in what is selected on screen
Set UserSelection = ActiveWindow.Selection
'Determine if selection is a shape
On Error GoTo NoShapeSelected
Set ActiveShape = ActiveSheet.Shapes(UserSelection.Name)
On Error Resume Next
'Tell User the Shape Position Values
MsgBox "Left Position = " & ActiveShape.Left & vbNewLine & _
"Top Position = " & ActiveShape.Top
Exit Sub
'Error Handler
NoShapeSelected:
MsgBox "You do not have a shape selected!"
End SubDetermining Shape Size
There are two properties you can modify to change the size of a shape with VBA. These two properties are the Width and Height values of the shape.
If you are unsure what the size of your shape should be, there are two popular ways you can size your shape:
Method 1: You can base it on the size of a range of cells
Sub ShapeSizeFromRange()
Dim shp As Shape
Dim rng As Range
'Provide Range for Shape Size
Set rng = Range("A1:C4")
'Create Shape
Set shp = ActiveSheet.Shapes.AddShape(msoShapeRectangle, _
ActiveCell.Left, ActiveCell.Top, rng.Width, rng.Height)
End SubMethod 2: You can create the shape to your liking manually and read the width and height using VBA
Sub DetermineShapeSize()
'PURPOSE: Display Selected Shape's Size
'SOURCE: www.TheSpreadsheetGuru.com
Dim ActiveShape As Shape
Dim UserSelection As Variant
'Pull-in what is selected on screen
Set UserSelection = ActiveWindow.Selection
'Determine if selection is a shape
On Error GoTo NoShapeSelected
Set ActiveShape = ActiveSheet.Shapes(UserSelection.Name)
On Error Resume Next
'Tell User the Shape Position Values
MsgBox "Width = " & ActiveShape.Width & vbNewLine & _
"Height = " & ActiveShape.Height
Exit Sub
'Error Handler
NoShapeSelected:
MsgBox "You do not have a shape selected!"
End SubText Formatting
Sub CreateShapeWithText()
Dim shp As Shape
'Create & Store New Shape to Variable
Set shp = ActiveSheet.Shapes.AddShape(msoShape16pointStar, _
ActiveCell.Left, ActiveCell.Top, 80, 27)
'Add Text To Shape
shp.TextFrame2.TextRange.Text = "My Awesome Shape!"
'Bold/Italicize/Underline Text
shp.TextFrame2.TextRange.Font.Bold = True
shp.TextFrame2.TextRange.Font.Italic = True
shp.TextFrame2.TextRange.Font.UnderlineStyle = msoUnderlineDottedLine
'Change Text Color
shp.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(225, 140, 71)
'Change Text Size
shp.TextFrame2.TextRange.Font.Size = 14
'Center Align Text
shp.TextFrame.HorizontalAlignment = xlHAlignCenter
shp.TextFrame.VerticalAlignment = xlVAlignCenter
End SubFill Color & Borders
Sub CreateShapeWithBorder()
Dim shp As Shape
'Create & Store New Shape to Variable
Set shp = ActiveSheet.Shapes.AddShape(msoShapeRoundedRectangle, _
ActiveCell.Left, ActiveCell.Top, 80, 27)
'Light Orange Fill
shp.Fill.ForeColor.RGB = RGB(253, 234, 218)
'Add Dotted Border
shp.Line.DashStyle = msoLineDashDotDot
'Dark Orange Border
shp.Line.ForeColor.RGB = RGB(252, 213, 181)
'Adjust Border Thickness
shp.Line.Weight = 2
'Remove Border
shp.Line.Visible = False
End SubChange Shape Type
If you are looking to change the shape type of an existing type, you can do so by setting the AutoShapeType to a different shape type value.
Sub ChangeShapeType()
Dim shp As Shape
'Store specific shape on spreadsheet to a variable
Set shp = ActiveSheet.Shapes("Shape1")
'Change shape type to oval
shp.AutoShapeType = msoShapeOval
End SubCreate Your Own Macro Button With VBA Code
I personally cannot stand the native Excel form control button. It looks so outdated and really makes your spreadsheets look unprofessional. That is why I prefer to use VBA code to create a shape that looks like a button.
I thought this would be a great example to show you a real-world coding example where I need to create and format a shape to have a specific appearance. The following VBA macro code puts everything we have covered in this guide together and provides you with some sample code that comprises of a true shape-building solution.
Sub Create_Button()
'PURPOSE: Creates a SpreadsheetGuru macro button shape
'SOURCE: www.TheSpreadsheetGuru.com
Dim bttn As Shape
'Create & Position Macro Button
Set bttn = ActiveSheet.Shapes.AddShape(msoShapeRoundedRectangle, _
ActiveCell.Left, ActiveCell.Top, 80, 27)
'Modify Text Formatting
With bttn.TextFrame2.TextRange
.Text = "Macro"
.Font.Bold = msoTrue
.Font.Fill.ForeColor.RGB = RGB(0, 0, 0)
.Font.Size = 14
End With
'Center Alignment
bttn.TextFrame.HorizontalAlignment = xlHAlignCenter
bttn.TextFrame.VerticalAlignment = xlVAlignCenter
'Light Gray Fill
bttn.Fill.ForeColor.RGB = RGB(217, 217, 217)
'No Border
bttn.Line.Visible = msoFalse
End SubLoop Through All Shapes Of Specific Type
If you need to target a specific shape type on your spreadsheet, you can create a loop that tests the AutoShapeType value to filter your results.
The following VBA code example loops through all shape objects in the currently selected spreadsheet and only changes the fill color of the rectangle shapes.
Sub ChangeRectangleShapes()
Dim shp As Shape
'Loop through each shape on ActiveSheet
For Each shp In ActiveSheet.Shapes
'Only modify rectangle shapes
If shp.AutoShapeType = msoShapeRectangle Then
shp.Fill.ForeColor.RGB = RGB(253, 234, 218)
End If
Next shp
End SubI Hope This Microsoft Excel Article Helped!
Hopefully, I was able to explain how you use VBA code to create and format shapes on your Excel spreadsheets. If you have any questions about these techniques or suggestions on how to improve them, please let me know in the comments section below.
After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!
Keep Learning
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.