Excel VBA for Shape Position (4 Examples)

Excel VBA Shape Position

Microsoft Excel is a powerful tool for organizing and analyzing data, and its VBA programming language allows for even greater flexibility and customization. One aspect of Excel VBA that can be particularly useful is the ability to manipulate shapes within a worksheet, such as moving them to specific positions or extracting the position of the shape. In this article, we will learn how to move, relocate or extract with Excel VBA for shape position.

How to Launch VBA Editor in Excel

To open the VBA code editor in Excel, you can utilize the keyboard shortcut. Let’s see the process.

  • Press Alt + F11 to open your Microsoft Visual Basic.

opening of VBA editor in Excel worksheet

  • Then press Insert > Module to open a blank module.

Vba Editor interface

Excel VBA for Shape Position: 4 Different Cases

Shapes are one of the many objects that can be added to an Excel worksheet, such as images, text boxes, and buttons. In VBA, the position of a shape can be manipulated through its properties, such as Top, Left, Width, and Height. These properties allow the programmer to precisely position a shape on the worksheet, and to dynamically adjust its position based on user input or other factors.

1. Importing Excel VBA Shape in Specific Position

The following VBA code creates a dashboard worksheet in the current workbook and adds two rectangles by using AddShape and two labels to it thereafter.

Code for Importing Excel VBA Shape in Specific Position

Sub CreateDashboard()
' Create a new worksheet to hold the dashboard
Dim dashboard As Worksheet
Set dashboard = ThisWorkbook.Worksheets.Add
dashboard.Name = "Add Shape"
' Define the size and position of the shapes
Dim shapeWidth As Integer
shapeWidth = 200
Dim shapeHeight As Integer
shapeHeight = 100
Dim shapeTop As Integer
shapeTop = 100
Dim shapeLeft1 As Integer
shapeLeft1 = 100
Dim shapeLeft2 As Integer
shapeLeft2 = 350
' Add the shapes to the dashboard
Dim shape1 As Shape
Set shape1 = dashboard.Shapes.AddShape(msoShapeRectangle, shapeLeft1, shapeTop, shapeWidth, shapeHeight)
shape1.Fill.ForeColor.RGB = RGB(255, 0, 0)
Dim shape2 As Shape
Set shape2 = dashboard.Shapes.AddShape(msoShapeRectangle, shapeLeft2, shapeTop, shapeWidth, shapeHeight)
shape2.Fill.ForeColor.RGB = RGB(0, 255, 0)
' Add labels to the shapes
Dim label1 As Shape
Set label1 = dashboard.Shapes.AddTextbox(msoTextOrientationHorizontal, shapeLeft1, shapeTop - 30, shapeWidth, 20)
label1.TextFrame.Characters.Text = "Shape 1"
label1.TextFrame.HorizontalAlignment = xlHAlignCenter
Dim label2 As Shape
Set label2 = dashboard.Shapes.AddTextbox(msoTextOrientationHorizontal, shapeLeft2, shapeTop - 30, shapeWidth, 20)
label2.TextFrame.Characters.Text = "Shape 2"
label2.TextFrame.HorizontalAlignment = xlHAlignCenter
End Sub

Code Breakdown


Create a new worksheet to hold the dashboard.

shapeWidth, shapeHeight, shaeTop, shapeLeft1, shapeLeft2

variables are used to set the size and position of the shapes that will be added to the worksheet.

Set shape1 = dashboard.Shapes.AddShape(msoShapeRectangle, shapeLeft1, shapeTop, shapeWidth, shapeHeight)shape1.Fill.ForeColor.RGB = RGB(255, 0, 0)

adds two shapes to the worksheet using the AddShape method. The first shape is a red rectangle, and the second shape is a green rectangle.


Add labels to the two shapes using the AddTextbox method. The labels are positioned above the shapes, and their text is set to Shape 1 and Shape 2, respectively. The horizontal alignment of the labels is centered using the HorizontalAlignment property.

Output of Excel VBA Shape in Specific Position

2. Determining Shape Position Through Cell Address

Dummy dashboard

The image given above depicts a dummy dashboard to articulate the topic of discussion. Here three rectangular shapes with different colors and texts have been added to provide a dashboard-like feature.

Now the following VBA code retrieves the location of a selected shape on a worksheet by checking the type of the selection and displaying its address accordingly.

VBA code for Determining Excel VBA Shape Position Through Cell Address

Sub Shape_Position_with_Cell_Location()
Dim shp As Shape, typ As String
typ = TypeName(Selection)
If typ = "Range" Then
MsgBox " Your Selected Shape Located at: " & Selection.Address
Set shp = ActiveSheet.Shapes(Selection.Name)
MsgBox "Your Selected Shape Located at: " & shp.TopLeftCell.Address
End If
End Sub

Code Breakdown

  • The first line defines a sub-procedure named Shape_Position_with_Cell_Location.
  • The typ variable is assigned the result of the TypeName This function returns the type of the selected object as a string.
  • An If statement is used to check whether the selected object is a range or a shape. If it is a range, a message box is displayed that shows the address of the selected range. If it is a shape, the next block of code is executed.
  • A message box is displayed that shows the address of the top-left cell of the selected shape.
  • The End Sub statement marks the end of the sub-procedure.

3. Discovering Shape Position

Dummy dashboard

Now, if you would like to know your desired shape position differently, then the following VBA code may serve you well. Here, we will select any of the shapes provided in the picture and retrieve their location in terms of Left Position and Top Position.

Code for Discovering Excel VBA Shape Position

Sub Extracting_Shape_Position()
Dim ActiveShape As Shape
Dim shape1 As Variant
Set shape1 = ActiveWindow.Selection
On Error GoTo NoSelectedShape
Set ActiveShape = ActiveSheet.Shapes(shape1.Name)
On Error Resume Next
MsgBox "Left Position = " & ActiveShape.Left & vbNewLine & _
"Top Position = " & ActiveShape.Top
Exit Sub
MsgBox "You do not have a shape selected!
End Sub

Code Breakdown

  • In the first line, the ActiveShape variable is declared and will be used to store the selected shape.
  • The second line declares a variable called shape1 as a Variant data type. This variable will be used to store the selection made by the user.
  • The third line assigns the value of the user’s selection to the shape1 variable using Selection property.
  • The On Error GoTo NoSelectedShape statement sets up an error handler. If an error occurs when attempting to retrieve the selected shape, the code will jump to the NoSelectedShape.
  • The Set ActiveShape = ActiveSheet.Shapes(shape1.Name) line attempts to set the ActiveShape variable to the selected shape. If the selection is not a shape or if an error occurs, the error handler will be triggered.
  • The On Error Resume Next statement resumes normal error-handling behavior.
  • The MsgBox statement displays a message box with the left and top positions of the selected shape.
  • The Exit Sub statement exits the subroutine.
  • The NoSelectedShape label is where the code jumps if an error occurs. It displays a message box notifying the user that no shape has been selected.

Output image of Discovering Excel VBA Shape Position

4. Viewing Detailed Specification of Shape Position in Excel VBA

Dummy dashboard

Following the previous example, we would like to make the code brief. This means we will now rewrite the VBA code to get the other properties associated with the shape like shape type, location, Forecolor, transparency, etc.

Code for Viewing Detailed Specification of Excel VBA Shape with Position

Sub Detailed_Shape_info()
Dim shape1 As Shape
Dim LabelText As String
Set shape1 = ActiveSheet.Shapes(3)
With shape1
LabelText = "Name: " & .Name & vbCrLf & _
"Text: " & .TextFrame.Characters.Text & vbCrLf & _
"Left: " & .Left & vbCrLf & _
"Top: " & .Top & vbCrLf & _
"Width: " & .Width & vbCrLf & _
"Height: " & .Height & vbCrLf & _
"Visible: " & IIf(.Fill.Visible, "True", "False") & vbCrLf & _
"Fore Color: " & .Fill.ForeColor.RGB & vbCrLf & _
"Transparency: " & Format(.Fill.Transparency, "0%") & vbCrLf & _
"Border visible: " & IIf(.Line.Visible, "True", "False") & vbCrLf & _
"Type: " & GetType(.Type)
If .Type = msoAutoShape Then
LabelText = LabelText & " (" & GetAutoShapeType(.AutoShapeType) & ")"
End If
End With
MsgBox LabelText, vbInformation
End Sub
Function GetType(shape1Type As MsoShapeType) As String
Select Case shape1Type
Case 1
GetType = "AutoShape"
Case 2
GetType = "Callout"
Case 3
GetType = "Chart"
Case 4
GetType = "Comment"
Case 5
GetType = "FreeForm"
Case 6
GetType = "Group"
Case 7
GetType = "Embedded OLE Object"
Case 8
GetType = "Form Control"
Case 9
GetType = "Line"
Case 10
GetType = "Linked OLE Object"
Case 11
GetType = "Linked Picture"
Case 12
GetType = "OLE Control"
Case 13
GetType = "Picture"
Case Else
GetType = "Other"
End Select
End Function
Function GetAutoShapeType(shape1Type As MsoAutoShapeType) As String
Select Case shape1Type
Case 1
GetAutoShapeType = "Rectangle"
Case 2
GetAutoShapeType = "Parallelogram"
Case 3
GetAutoShapeType = "Trapezoid"
Case 4
GetAutoShapeType = "Diamond"
Case 5
GetAutoShapeType = "Rounded Rectangle"
Case 6
GetAutoShapeType = "Octagon"
Case 7
GetAutoShapeType = "Isosceles Triangle"
Case 8
GetAutoShapeType = "Right Triangle"
Case 9
GetAutoShapeType = "Oval"
Case 10
GetAutoShapeType = "Hexagon"
Case 11
GetAutoShapeType = "Cross"
Case 12
GetAutoShapeType = "Regular Pentagon"
Case 13
GetAutoShapeType = "Can"
Case Else
GetAutoShapeType = "Other"
End Select
End Function

Code Breakdown

  • Firstly, two variables shape1 and LabelText are defined. The shape1 variable will be used to store the shape being inspected, and LabelText will be used to store the text that will be displayed in a message box.
  • Set shape1 = ActiveSheet.Shapes(3) sets the “shape1” variable to the third shape in the active worksheet. This line is just an example and can be changed to select any specific shape.
  • Next, With statement is used to apply a series of property assignments to the shape1 This statement is followed by a series of lines that extract specific properties of the shape and store them in the LabelText variable.
  • The MsgBox statement displays the LabelText variable in a message box with the vbInformation argument indicating that it should be displayed with an information icon.
  • Two functions are defined below the macro: GetType and GetAutoShapeType. These functions take a numeric argument representing the type of shape or auto shape, respectively, and return a string describing the shape type.

Output image of Viewing Detailed Specification of Excel VBA Shape with Position

Here Rectangle 3 indicates the leftmost rectangle containing the commencement year of Exceldemy.

Frequently Asked Questions

  • How do I change the position of a shape in Excel?

To change the position of a shape in Excel, select it, click and hold on the border of the shape, drag it to its new position, and release the mouse button. Alternatively, you can follow the View tab in the Show group, then select Task Panes > Size & Position to change the position.

  • Where is the location of the shape in Excel?

The location of a shape in Excel refers to its position on the worksheet or chart. You can view and adjust the location of a shape in Excel by selecting it and checking its position in the Size & Properties or Format tab on the ribbon.

  • How do I set a shape object in Excel VBA?

To set a shape object in Excel VBA, you can use the following code:

Dim shp As Shape
Set shp = ActiveSheet.Shapes("Shape Name")

This code declares a variable shp as a Shape object and assigns it to the shape with the name Shape Name on the active sheet.

Download Practice Workbook

You can download and practice the dataset that we have used to prepare this article.


In this article, we have discussed different cases of using Excel VBA for shape position. If you are developing custom applications in Excel, you may need to interact with shapes on the worksheet. knowing the VBA shape position is essential to make your task easy.  Further, If you have any queries, feel free to comment below and we will get back to you soon.

Related Articles

<< Go Back to Excel Shapes | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!


Mohammad Shah Miran
Mohammad Shah Miran

Mohammad Shah Miran has a professional background spanning over a year at Softeko. Initially starting as an Excel and VBA writer, he authored more than 50 articles for the ExcelDemy project. Currently, Miran is engaged in the LinuxSimply project as a Linux content developer, completed over 40 articles. His analytical approach extends across various domains, including Excel, VBA, Bash scripting, Linux, data analysis, and Python programming. In his leisure time, Miran enjoys watching movies and series or listening... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF