Excel VBA for Shape Position (4 Examples)

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.

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

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.

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

Worksheets.Add

shapeWidth, shapeHeight, shaeTop, shapeLeft1, shapeLeft2

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

TextFrame.Characters.Text

2. Determining Shape Position Through Cell Address

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.

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
Else
Set shp = ActiveSheet.Shapes(Selection.Name)
MsgBox "Your Selected Shape Located at: " & shp.TopLeftCell.Address
End If
End Sub

3. Discovering Shape Position

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.

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
NoSelectedShape:
MsgBox "You do not have a shape selected!
End Sub

4. Viewing Detailed Specification of Shape Position in Excel VBA

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.

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

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.

Conclusion

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

• How to Align Shapes in Excel
• How to Create a Word Cloud in Excel

<< Go Back to Excel Shapes | Learn Excel