ActiveSheet.Shapes
 

Hi NG,

I have the following code that removes my commandbuttons on "save", it's a
XLT file with
3 sheets, my problem is that it only removes the commandbuttons on the
active sheet and
i need it to remove the commandbuttons on all 3 sheets in my workbook.

I can't seem to find anything were you can do it on all sheets??

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
'----------------------------------------------------------------
'Sub RemoveShapes()
'----------------------------------------------------------------
Dim shp As Shape
Dim sTopLeft As String
Dim fOK As Boolean

For Each shp In ActiveSheet.Shapes

fOK = True

sTopLeft = ""
On Error Resume Next
sTopLeft = shp.TopLeftCell.Address
'Autofilter and Data Validation dropdowns
'don't seem to have a topleftcell address.
On Error GoTo 0

If shp.Type = msoFormControl Then
If shp.FormControlType = xlDropDown Then
If sTopLeft = "" Then
'keep it
fOK = False
End If
End If
End If

If fOK Then
shp.Delete
End If

Next shp

End Sub

AHA.
Ole

ActiveSheet.Shapes
 

If you're just getting rid of commandbuttons from the control toolbox toolbar,
it may be better to just go after them:

Option Explicit
Sub testme()

Dim OLEObj As OLEObject
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
For Each OLEObj In wks.OLEObjects
If TypeOf OLEObj.Object Is MSForms.CommandButton Then
OLEObj.Delete
End If
Next OLEObj
Next wks

End Sub



ole_ wrote:

Hi NG,

I have the following code that removes my commandbuttons on "save", it's a
XLT file with
3 sheets, my problem is that it only removes the commandbuttons on the
active sheet and
i need it to remove the commandbuttons on all 3 sheets in my workbook.

I can't seem to find anything were you can do it on all sheets??

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
'----------------------------------------------------------------
'Sub RemoveShapes()
'----------------------------------------------------------------
Dim shp As Shape
Dim sTopLeft As String
Dim fOK As Boolean

For Each shp In ActiveSheet.Shapes

fOK = True

sTopLeft = ""
On Error Resume Next
sTopLeft = shp.TopLeftCell.Address
'Autofilter and Data Validation dropdowns
'don't seem to have a topleftcell address.
On Error GoTo 0

If shp.Type = msoFormControl Then
If shp.FormControlType = xlDropDown Then
If sTopLeft = "" Then
'keep it
fOK = False
End If
End If
End If

If fOK Then
shp.Delete
End If

Next shp

End Sub

AHA.
Ole

--

Dave Peterson