I have the code below which removes Form Control buttons, but it also
removes Charts I have on the sheet, how can I adjust the code below,
which will allow me to still remove the buttons but not the Charts?


Sub ClearMacroButtons()

On Error Resume Next
Sheets("Report").Select
ActiveSheet.DrawingObjects.Visible = True
ActiveSheet.DrawingObjects.Delete
On Error GoTo 0
End Sub

If the buttons are from the Forms toolbar, you could use:

Dim BTN as button
for each btn in worksheets("report").buttons
btn.delete
next btn


If the commandbuttons are from the Control toolbox toolbar, you could use:

Dim OLEObj As OLEObject
For Each OLEObj In Worksheets("report").OLEObjects
If TypeOf OLEObj.Object Is MSForms.commandbutton Then
OLEObj.Delete
End If
Next OLEObj


On 07/11/2010 10:40, Seanie wrote:
> I have the code below which removes Form Control buttons, but it also
> removes Charts I have on the sheet, how can I adjust the code below,
> which will allow me to still remove the buttons but not the Charts?
>
>
> Sub ClearMacroButtons()
>
> On Error Resume Next
> Sheets("Report").Select
> ActiveSheet.DrawingObjects.Visible = True
> ActiveSheet.DrawingObjects.Delete
> On Error GoTo 0
> End Sub

--
Dave Peterson

Thanks works like a dream, but how do I tweak to work through several
sheet? My existing code is

Sub ClearMacroButtons()

On Error Resume Next
Sheets(Array("E-Mail1", "E-Mail2", "E-Mail3", "E-Mail4", "E-
Mail5")).Select
ActiveSheet.DrawingObjects.Visible = True
ActiveSheet.DrawingObjects.Delete

On Error GoTo 0
End Sub

I tried, but it debugs

Sub ClearMacroButtons()
Dim BTN as button
for each btn in Sheets(Array("E-Mail1", "E-Mail2", "E-Mail3", "E-
Mail4", "E-Mail5")).buttons
btn.delete
next btn
End Sub

Option Explicit
Sub ClearMacroButtons()
Dim WksNames As Variant
Dim BTN As Button
Dim wCtr As Long

WksNames = Array("E-Mail1", "E-Mail2", "E-Mail3", "E-Mail4", "E-Mail5")

For wCtr = LBound(WksNames) To UBound(WksNames)
For Each BTN In Worksheets(WksNames(wCtr)).Buttons
BTN.Delete
Next BTN
Next wCtr

End Sub



On 07/12/2010 01:08, Seanie wrote:
> Thanks works like a dream, but how do I tweak to work through several
> sheet? My existing code is
>
> Sub ClearMacroButtons()
>
> On Error Resume Next
> Sheets(Array("E-Mail1", "E-Mail2", "E-Mail3", "E-Mail4", "E-
> Mail5")).Select
> ActiveSheet.DrawingObjects.Visible = True
> ActiveSheet.DrawingObjects.Delete
>
> On Error GoTo 0
> End Sub
>
> I tried, but it debugs
>
> Sub ClearMacroButtons()
> Dim BTN as button
> for each btn in Sheets(Array("E-Mail1", "E-Mail2", "E-Mail3", "E-
> Mail4", "E-Mail5")).buttons
> btn.delete
> next btn
> End Sub

--
Dave Peterson