Code to Remove Form Control buttons Q
 

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

Code to Remove Form Control buttons Q
 

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

Code to Remove Form Control buttons Q
 

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

Code to Remove Form Control buttons Q
 

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