How to Insert Shapes in Excel Using VBA?

A shape is an object in excel. This is the ready-made shape. There are different types of shape in Excel. We use the shape in Excel for designing purpose. We can use the shape in Chard, Dashboard.

In this blog, we learn about how to insert shape in excel using VBA and format shape. Here are a few examples based on shapes.

You can choose many types of Shapes from this syntax (Shapes.AddShape(msoShapeRectangle, ShapeLeft, ShapeTop, ShapeWidth, ShapeHeight) ) you have to change msoShapeRectabgle to msoShapeRoundedRectangle and many more. You can see after writing this Shapes.AddShape(,,,,) when you open the first parenthesis then you see many options for shapes types and after this, you have to mention an area of shapes like Left, Top, Width, Height. 

Here are given some example to insert shapes in excel using VBA

How to Insert Shapes in Excel Using VBA?

Sub Add_Single_Shape()

    Dim sh_Obj As Shape

    Set sh_Obj_Obj = Sheet1.Shapes.AddShape(msoShapeHeart, 20, 20, 72, 72)

    sh_Obj_Obj.Fill.ForeColor.RGB = rgbHotPink

End Sub

How to Insert Picture in Excel Using VBA?

Sub Insert_New_Picture()

    Dim sh_Obj As Shape

    Set sh_Obj = Sheet1.Shapes.AddPicture2( _

        Filename:=Environ(“UserProfile”) & “\Desktop\AVIT.jpg”, _

        LinkToFile:=msoFalse, _

        SaveWithDocument:=msoTrue, _

        Left:=100, Top:=20, Width:=-1, Height:=-1, _

        Compress:=msoPictureCompressTrue)  

    sh_Obj.LockAspectRatio = msoTrue

    sh_Obj.Width = 100

End Sub

How to Insert Shapes in Excel Using VBA?

Sub Insert_New_Button()

    Dim sh_Obj As Shape

    Set sh_Obj = Sheet1.Shapes.AddFormControl(xlButtonControl, 50, 100, 200, 50)

End Sub

How to Delete Shapes in Excel Using VBA?

Sub Delete_Active_Shapes()

    Dim sh_Obj As Shape

    For Each sh_Obj In ActiveSheet.Shapes

        sh_Obj.Delete

    Next sh_Obj

End Sub

How to Delete all Shapes in Excel Using VBA?

Sub Delete_All_Shapes()

    Dim ws As Worksheet

    Dim sh_Obj As Shape

    For Each ws In Worksheets

        For Each sh_Obj In ws.Shapes

            sh_Obj.Delete

        Next sh_Obj

    Next ws

End Sub

How to show Shapes name in MsgBox VBA?

Sub Extract_Shape_Name1()

    Dim sh_Obj As Shape

    For Each sh_Obj In Sheet1.Shapes

        MsgBox sh_Obj.Name

    Next sh_Obj

End Sub

How to show Shapes name in MsgBox VBA?

Sub Extract_Shapes_Name2()

    Dim i As Integer

    For i = 1 To Sheet1.Shapes.Count

        MsgBox Sheet1.Shapes(i).Name

    Next i

End Sub

 

How to insert multiple shapes in Excel Sheet using VBA?

Sub Add_Multiple_Shapes_In_Excel_Sheet()   

    Application.DisplayAlerts = False

    Application.ScreenUpdating = False

    Application.Calculation = xlCalculationManual

    Application.EnableEvents = False

    Dim sh_ObjapePic As Shape

    Dim i As Long

    Dim j As Long

    Dim ShapeLeft As Long

    Dim ShapeTop As Long

    Const ShapeWidth As Long = 48

    Const ShapeHeight As Long = 45

    For j = 0 To 3

        ShapeTop = j * ShapeHeight

        For i = 0 To 2

            ShapeLeft = i * ShapeWidth

            Set sh_ObjapePic = _

            Sheet1.Shapes.AddShape(msoShapeRectangle, ShapeLeft, ShapeTop, ShapeWidth, ShapeHeight)

        Next i

    Next j

    Application.EnableEvents = True

    Application.DisplayAlerts = True

    Application.ScreenUpdating = True

    Application.Calculation = xlCalculationAutomatic

End Sub

 

How to insert Text in Excel Sheet using VBA?

Sub Insert_Single_Text()

    Dim sh_Object As Shape

    Set sh_Object = Sheet1.Shapes.AddShape(msoShapeRectangle, 20, 20, 200, 100)

    sh_Object.TextFrame2.TextRange.Text = “AVIT Guru”

    sh_Object.Fill.ForeColor.RGB = rgbYellow

End Sub

 

How to insert form button in Excel Sheet using VBA?

Sub Insert_Form_Button()

    Dim sh_Object As Shape

    Set sh_Object = Sheet1.Shapes.AddFormControl(xlButtonControl, 20, 20, 100, 50)

    sh_Object.TextFrame.Characters.Text = “Click Here” 

End Sub

 

How to insert Textbox in Excel Sheet using VBA?

Sub Insert_Basic_Textbox_And_Label()

    Dim sh_Object As Shape

    Set sh_Object = Sheet1.Shapes.AddLabel(msoTextOrientationHorizontal, 20, 20, 200, 100)

    sh_Object.TextFrame2.TextRange.Text = “AVIT Guru”

End Sub

 

How to insert Word Art in Excel Sheet using VBA?

Sub Insert_WordArt()

    Dim sh_Object As Shape

    Set sh_Object = Sheet1.Shapes.AddTextEffect( _

        PresetTextEffect:=msoTextEffect27, _

        Text:=”This is Akash From AVIT Guru”, _

        FontName:=”Verdana”, _

        FontSize:=18, _

        FontBold:=msoFalse, _

        FontItalic:=msoFalse, _

        Left:=20, _

        Top:=20)   

End Sub

How to use Data Validation in Excel from VBA?

Download Workbook

Leave a Reply

Your email address will not be published. Required fields are marked *