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
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
[vba]
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
[/vba]
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Similar to Bob's, this also gets the OLEObjects:[vba]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[/vba]
Matt
Bob - What is the sTest there for?
Matt
So does mine, and mine doesn't remove data validation and autofilter arrows.Originally Posted by mvidas
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
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
Matt
thank you very much to both of you - it works perfectly fine!
and ok, i'll use xld's code..