Delete or Hide Shapes | Mac Excel Automation

Delete or Hide Shapes on a worksheet

Shapes collection
Members of the Shapes collection are for example:
1. Forms Controls from the Developer tab
2. Pictures, Shapes, charts, Insert Tab > Illustrations group

You see that all objects/controls are a member of the Shapes collection.
Below you find examples to delete or hide the members of this collection.
Tip: if you only want to hide all shapes for a moment then you can use the toggle shortcut Ctrl 6 (This shortcut is working for the whole workbook)

Manual Delete shapes
If you want to delete all objects/controls on a worksheet you can do it manual like this :
1. Press F5
2. Click on Special
3. Choose Objects
4. OK
5. Press the Delete button

Tip: You can do the same for Notes in the F5>Special dialog. (Not working on this moment on a Mac for Comments)

VBA code examples

Delete all shapes
Use this macro to delete all shapes on the worksheet, working in all Excel versions. The second macro you can use to Delete Notes and Comments on your worksheet.

Delete only specific shapes
What if you only want to delete Pictures or Forms controls.
You can loop through the collection and check the Type of the control.

12 = ActiveX control (control toolbox) or a linked or embedded OLE object. (Not exists in Mac Office)
13 = Picture
8 = Forms controls

For Type 8 we use the second macro in Excel for Windows to avoid the problem of losing Data Validation drop-downs on your worksheet. You not have to use the second macro on your Mac because we not have this problem in Mac Excel.

Sub Shapes2()
'Loop through the Shapes collection and use the Type number of the control
    Dim myshape As Shape
    For Each myshape In ActiveSheet.Shapes
 
        ' 8 = Forms controls
        If myshape.Type = 8 Then myshape.Delete
        ' You can also use  myshape.Visible = False
 
    Next myshape
End Sub

Sub Shapes3()
'Dave Peterson and Bob Phillips
'Example only for the Forms controls type 8 in Windows
'To avoid losing data validation dropdowns in Excel for Windows
    Dim shp As Shape
    Dim testStr As String

    For Each shp In ActiveSheet.Shapes

        If shp.Type = 8 Then
            If shp.FormControlType = 2 Then
                testStr = ""
                On Error Resume Next
                testStr = shp.TopLeftCell.Address
                On Error GoTo 0
                If testStr <> "" Then shp.Delete
            Else
                shp.Delete
            End If
        End If

    Next shp
End Sub

In the workaround macro above for Excel for Windows we use FormControlType = 2 in the loop (xlDropDown). Data Validation drop-downs do not have a TopLeftCell.Address and the macro will not delete this DropDowns.

Delete or Hide one shape
Because all objects/controls are a member of the shapes collection we can use this to delete or hide one button, picture or ?

Another way: You can also point to the Forms control like ActiveSheet.Buttons, Instead of Buttons you can also use for example OptionButtons, CheckBoxes or DropDowns, see the examples below.

VBA code examples

If you want to know all the Type numbers of all Shapes on your worksheet you can run this macro to add a new worksheet with the names and Type numbers of all Shapes on your worksheet. You can find the number then that you must use in the code to Hide or delete the Shapes you want.

Sub ListAllShapesActiveSheet()
    Dim NewSheet As Worksheet
    Dim MySheet As Worksheet
    Dim myshape As Shape
    Dim I As Long

    Set MySheet = ActiveSheet
    Set NewSheet = Worksheets.Add

    With NewSheet
        .Range("A1").Value = "Name"
        .Range("B1").Value = "Visible(-1) or Not Visible(0)"
        .Range("C1").Value = "Shape type"
        I = 2

        For Each myshape In MySheet.Shapes
            .Cells(I, 1).Value = myshape.Name
            .Cells(I, 2).Value = myshape.Visible
            .Cells(I, 3).Value = myshape.Type
            I = I + 1
        Next myshape

        .Range("A1:C1").Font.Bold = True
        .Columns.AutoFit
        .Range("A1:C" & Rows.Count).Sort Key1:=Range("C1"), _
                        Order1:=xlAscending, Header:=xlYes
    End With

End Sub