Delete or Hide Objects/Controls on a worksheet

Important message to visitors of this page

Ron de Bruin decided to remove all Windows Excel content from his website for personal reasons. If you want to know why, head over to rondebruin.nl.

Luckily, Ron was kind enough to allow me to publish all of his Excel content here.

Most of these pages are slightly outdated and may contain links that don 't work. Please inform me if you find such an error and I'll try to fix it.

Kind regards

Jan Karel Pieterse

Shapes collection

Members of the Shapes collection are:

1. ActiveX controls (Control Toolbox) or a linked or embedded OLE objects
2. Controls from the Forms toolbar
3. Controls from the Drawing toolbar
4. Pictures, charts, ..................

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 is for the whole workbook)

Manual delete shapes

Excel 97-2003 and Excel 2010-2016 (not Excel 2007)

If you want to delete all objects/controls on a worksheet you can do it manual like this in 97-2003, 2010-2016:

1. Press F5
2. Click on Special
3. Choose Objects
4. OK
5. Press the Delete button

You can do the same for Comments in the F5>Special dialog.

Note: for Activex(control toolbox) controls you must be in "Design Mode" in Excel 97-2003. Use the first button on the Control toolbox toolbar to toggle this mode.

Excel 2007

In Excel 2007 there is no way to select all shapes.
F5>Special>Objects will not select ActiveX and forms controls in Excel 2007.

With VBA code

Delete all shapes

Use this macro to delete all shapes on the worksheet, working in all Excel versions(also in 2007).

Sub Shapes1()
'Delete all Objects except Comments
    On Error Resume Next
    ActiveSheet.DrawingObjects.Visible = True
    ActiveSheet.DrawingObjects.Delete
    On Error GoTo 0
End Sub

Use this to delete comments

Sub Comments()
'This will delete all comments
    ActiveSheet.Cells.ClearComments
End Sub

Warning :
Not use code like below because it is possible that It will delete the AutoFilter dropdowns, It will delete the Data>Validation(List option) dropdowns, Excel crash if there are comments on the sheet
Note: Not every Excel versions have all problems.

Sub NotUseThisMacro()
'Delete every shape in the Shapes collection
    Dim myshape As Shape
    For Each myshape In ActiveSheet.Shapes
        myshape.Delete
    Next myshape
End Sub

Delete only specific shapes

What if you only want to delete control toolbox controls, 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.
13 = Picture
8 = Forms controls

For Type 8 we use another macro to avoid the problem of losing AutoFilter and Data Validation dropdowns on your worksheet.See the example in this section "Delete only Forms controls"

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
 
        ' ActiveX control (control toolbox) or a linked or embedded OLE object
        If myshape.Type = 12 Then myshape.Delete
        ' You can also use  myshape.Visible = False
 
    Next myshape
End Sub

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

Sub ListAllObjectsActiveSheet()
    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

Delete only Forms controls

This example avoid the problem of losing AutoFilter and Data Validation dropdowns on your worksheet when you use Type 8.

Sub Shapes4()
'Dave Peterson and Bob Phillips
'Example only for the Forms controls
    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 we use FormControlType = 2 in the loop (xlDropDown). AutoFilter and Data Validation dropdowns do not have TopLeftCell.Address and the macro will not delete this DropDowns.

Other FormControl constants are:
(only for the Forms controls)

xlButtonControl = 0
xlCheckBox = 1
xlDropDown = 2
xlEditBox = 3
xlGroupBox = 4
xlLabel = 5
xlListBox = 6
xlOptionButton = 7
xlScrollBar = 8
xlSpinner = 9

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 ?

Sub Delete_One_Shape()
    ActiveSheet.Shapes("YourShapeName").Delete
End Sub

Sub Hide_One_Shape()
    ActiveSheet.Shapes("YourShapeName").Visible = False
End Sub

Specific examples for Activex(control toolbox) or Forms controls

For most things the macros in the first section of this page are Ok but if you only want to delete Forms buttons or ActiveX buttons then look here for a few examples.

ActiveX controls (Control Toolbox) or linked or embedded OLE objects

Sub OLEObjects1()
'Hide all ActiveX controls(Control Toolbox)or linked or embedded OLE objects
    On Error Resume Next
    ActiveSheet.OLEObjects.Visible = False
    On Error GoTo 0
End Sub

Sub OLEObjects2()
'Delete all ActiveX controls(Control Toolbox)or linked or embedded OLE objects
    On Error Resume Next
    ActiveSheet.OLEObjects.Visible = True
    ActiveSheet.OLEObjects.Delete
    On Error GoTo 0
End Sub

Sub OLEObjects3()
'Delete/hide only all CommandButtons from the Control Toolbox
    Dim obj As OLEObject
    For Each obj In ActiveSheet.OLEObjects
        If TypeOf obj.Object Is MSForms.CommandButton Then
            obj.Delete
            ' or obj.Visible = False if you want to hide them
        End If
    Next
End Sub

Others are :

MSForms.CheckBox
MSForms.TextBox
MSForms.OptionButton
MSForms.ListBox
MSForms.ComboBox
MSForms.ToggleButton
MSForms.SpinButton
MSForms.ScrollBar
MSForms.Label
MSForms.Image

Sub OLEObjects4()
'Hide one ActiveX control(Control Toolbox)or a linked or embedded OLE object
    ActiveSheet.OLEObjects("CommandButton1").Visible = False
End Sub

Sub OLEObjects5()
'Delete one ActiveX control(Control Toolbox)or a linked or embedded OLE object
    ActiveSheet.OLEObjects("CommandButton1").Delete
End Sub

Because Control Toolbox controls are also a member of the Shapes collection you can also use this :

Sub OLEObjects6()
'Hide one Control Toolbox button or Control
    ActiveSheet.Shapes("CommandButton1").Visible = False
End Sub

Sub OLEObjects7()
'Delete one Control Toolbox button or Control
    ActiveSheet.Shapes("CommandButton1").Delete
End Sub

To clear textboxes or uncheck checkboxes you can use code like this :

Sub TestMe()
    Dim obj As OLEObject
    For Each obj In ActiveSheet.OLEObjects
        If TypeOf obj.Object Is MSForms.TextBox Then
            obj.Object.Text = ""
        End If
        If TypeOf obj.Object Is MSForms.CheckBox Then
            obj.Object.Value = False
        End If
    Next
End Sub

Forms controls

Sub Forms1()
'Delete All Forms buttons
    ActiveSheet.Buttons.Delete
End Sub

Sub Forms2()
'Hide All Forms buttons
    ActiveSheet.Buttons.Visible = False
End Sub

Sub Forms3()
'Delete one Forms button
    ActiveSheet.Buttons("Button 1").Delete
End Sub

Sub Forms4()
'Hide one Forms button
    ActiveSheet.Buttons("Button 1").Visible = False
End Sub

Instead of Buttons you can also use

OptionButtons
CheckBoxes
DropDowns

Because Forms controls are also a member of the Shapes collection you can also use this

Sub Forms5()
'One Forms button or Control
    ActiveSheet.Shapes("Button 1").Delete
End Sub

Sub Forms6()
'One Forms button or Control
    ActiveSheet.Shapes("Button 1").Visible = False
End Sub

Ron de BruinExcel Automation