getting rid of all user form objects in a workbook

[ardadogan]

can anyone think of a quick way to get rid of ALL the objects in a workbook - including buttons, combo boxes, radio buttons, etc etc?


many thanks,


arda

[Bob Phillips]

Sub DeleteShapes()
Dim oShape As Shape
Dim sTest As String
Dim OKToDelete As Boolean

For Each oShape In ActiveSheet.Shapes
OKToDelete = True

sTest = ""
On Error Resume Next
sTest = oShape.TopLeftCell.Address
On Error Goto 0

If oShape.Type = msoFormControl Then
If oShape.FormControlType = xlDropDown Then
If sTest = "" Then
'keep it
OKToDelete = False
End If
End If
End If

If OKToDelete Then
oShape.Delete
End If

Next oShape

End Sub

[mvidas]

Similar to Bob's, this also gets the OLEObjects:Sub ArdaStripWorkbook()
Dim WS As Worksheet, OLEO As OLEObject, SH As Shape
For Each WS In ActiveWorkbook.Worksheets
'for controls added by Control Toolbox toolbar
For Each OLEO In WS.OLEObjects
OLEO.Delete
Next 'oleo
'for shapes (Forms toolbar)
For Each SH In WS.Shapes
If SH.Type = 8 Then SH.Delete 'msoformcontrol
Next 'sh
Next 'ws
End Sub

[mvidas]

Bob - What is the sTest there for?

[Bob Phillips]

Similar to Bob's, this also gets the OLEObjects:Sub ArdaStripWorkbook()
Dim WS As Worksheet, OLEO As OLEObject, SH As Shape
For Each WS In ActiveWorkbook.Worksheets
'for controls added by Control Toolbox toolbar
For Each OLEO In WS.OLEObjects
OLEO.Delete
Next 'oleo
'for shapes (Forms toolbar)
For Each SH In WS.Shapes
If SH.Type = 8 Then SH.Delete 'msoformcontrol
Next 'sh
Next 'ws
End Sub So does mine, and mine doesn't remove data validation and autofilter arrows.

[mvidas]

Ohhhh I understand why you're testing the .Type now (sTest for the two aforementioned items?).. I missed one key line in your sub.

Arda, please disregard my above code :)

[ardadogan]

thank you very much to both of you - it works perfectly fine! :)

and ok, i'll use xld's code..