Delete or Hide Objects/Controls on a worksheet

Ron de Bruin (last update 29-July-2007)
Go back to the Excel tips page

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


Excel 97-2003

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

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

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

You can do the same for Comments.


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.
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()
'Loop through 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 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 one shape

Because all objects/controls are a member of the shapes collection we can use this
to delete 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

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