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

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