PDA

View Full Version : getting rid of all user form objects in a workbook



ardadogan
06-14-2007, 08:06 AM
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
06-14-2007, 08:41 AM
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
06-14-2007, 08:45 AM
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
06-14-2007, 08:47 AM
Bob - What is the sTest there for?

Bob Phillips
06-14-2007, 08:53 AM
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
06-14-2007, 08:59 AM
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
06-14-2007, 09:01 AM
thank you very much to both of you - it works perfectly fine! :)

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