My Blog

Adding/Deleting Shapes in a worksheet

3

In continuation from here

You can add shapes using the below code. The syntax is:

Sheet.Shapes.AddShape(Shape Type, Left, Top, Width, Height).Select()

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    '~~> Define your Excel Objects
    Dim xlApp As New Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        '~~> Opens Source Workbook. Change path and filename as applicable
        xlWorkBook = xlApp.Workbooks.Open("C:\Tutorial\Sample.xlsx")

        '~~> Display Excel
        xlApp.Visible = True

        '~~> Set the source worksheet
        xlWorkSheet = xlWorkBook.Sheets("Sheet1")

        '~~> This will insert a rectangular shape.
        xlWorkSheet.Shapes.AddShape(Microsoft.Office.Core.MsoAutoShapeType.msoShapeRectangle, 105.75, 54.75, 114, 65.25).Select()

        '~~> This will insert an oval shape
        xlWorkSheet.Shapes.AddShape(Microsoft.Office.Core.MsoAutoShapeType.msoShapeOval, 441, 57, 117.75, 90.75).Select()

        '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
        ' similarly you can use msoShapeSmileyFace to insert a smiley face shape '
        ' and msoShapeHeart to insert aHeart shape. the moment you type '
        ' Microsoft.Office.Core.MsoAutoShapeType and type a DOT (.), you will get'
        ' a list of all the shapes that you can draw '
        '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Me.Close()
    End Sub
End Class

To delete the shape that we created use this

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    '~~> Define your Excel Objects
    Dim xlApp As New Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        '~~> Opens Source Workbook. Change path and filename as applicable
        xlWorkBook = xlApp.Workbooks.Open("C:\Tutorial\Sample.xlsx")

        '~~> Display Excel
        xlApp.Visible = True

        '~~> Set the source worksheet
        xlWorkSheet = xlWorkBook.Sheets("Sheet1")

        '~~> Deleting the Shapes
        xlWorkSheet.Shapes.Range(New String() {"Oval 2"}).Delete()
        xlWorkSheet.Shapes.Range(New String() {"Rectangle 1"}).Delete()
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Me.Close()
    End Sub
End Class

For the above method you need to know the name of the shape. In case you want to delete all the shapes in the worksheet then use this code.

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    '~~> Define your Excel Objects
    Dim xlApp As New Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Dim shp As Excel.Shape

        '~~> Opens Source Workbook. Change path and filename as applicable
        xlWorkBook = xlApp.Workbooks.Open("C:\Tutorial\Sample.xlsx")

        '~~> Display Excel
        xlApp.Visible = True

        '~~> Set the source worksheet
        xlWorkSheet = xlWorkBook.Sheets("Sheet1")

        '~~> Loop through all the shapes in the worksheet and delete them
        For Each shp In xlWorkSheet.Shapes
            '~~> To avoid datavalidation shape getting deleted
            '~~> See comments by Brad below.
            If shp.Type <> 8 Then shp.Delete()
        Next
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Me.Close()
    End Sub
End Class
  1. Brad Yundt
    Brad Yundt05-19-2013

    It can be dangerous to delete all the shapes on a worksheet. The arrow for Data validation dropdowns is a member of the Shapes collection–and cannot be recovered if you delete it. Not only won’t existing dropdowns work, neither will you be able to install new ones on that worksheet. The only remedy is to copy all the content to a new worksheet and patch up the links as required.

    To avoid this pitfall, test the Type property of the shape object before deleting it. Data validation dropdowns have a Type property of 8 (msoFormControl).
    ‘~~> Loop through all the shapes in the worksheet and delete them
    For Each shp In xlWorkSheet.Shapes
    If shp.Type 8 Then shp.Delete()
    Next

  2. Brad Yundt
    Brad Yundt05-19-2013

    Should be an equals sign in the test for shp.Type

    • Siddharth Rout
      Siddharth Rout05-19-2013

      Good Point! I have updated the post above. :) Thanks Brad.