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)
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
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.
In Excel 2007 there is no way to select all shapes.
F5>Special>Objects will not select ActiveX and forms controls in Excel 2007.
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
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
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 :
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
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
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