How do I remove all buttons in VBA on a sheet?

[rpaolilloML]

I am trying to write a macro that will remove all form Buttons. Any help is apreciated.

Regards,
Rob

 

[Datsmart]

See this post:
http://www.mrexcel.com/board2/viewtopic.php?p=907205

 

[erik.van.geit]

Hi,

this code allows you to restrict
1. the range (which you can enlarge to "cells" or usedrange)
2. the type of controls to delete

Sub delete_controls_within_range()

Dim sh As Shape
Dim rng As Range

Set rng = Range("A1:D10")

    For Each sh In ActiveSheet.Shapes
      If sh.Type = msoFormControl Then
        If sh.FormControlType = xlButtonControl _
        And Not Intersect(sh.TopLeftCell, rng) Is Nothing Then sh.Delete
      End If
    Next sh
End Sub

more documented

Sub delete_controls_within_range()
'Erik Van Geit
'050517 0053
'msoOLEControlObjects
'''CheckBox
'''TextBox
'''CommandButton
'''OptionButton
'''ListBox
'''ComboBox
'''ToggleButton
'''SpinButton
'''ScrollBar
'''Label
'''Image

'msoFormControl
'''xlButtonControl
'''xlCheckBox
'''xlDropDown
'''xlEditBox
'''xlGroupBox
'''xlLabel
'''xlListBox
'''xlOptionButton
'''xlScrollBar
'''xlSpinner

Dim sh As Shape
Dim rng As Range

Set rng = Range("A1:D10")

    For Each sh In ActiveSheet.Shapes
      If sh.Type = msoOLEControlObject Then
        If TypeName(sh.OLEFormat.Object.Object) = "ComboBox" _
        And Not Intersect(sh.TopLeftCell, rng) Is Nothing Then sh.Delete
      End If
      If sh.Type = msoFormControl Then
        If sh.FormControlType = xlCheckBox _
        And Not Intersect(sh.TopLeftCell, rng) Is Nothing Then sh.Delete
      End If
    Next sh
End Sub

replace "combobox" and xlcheckbox with the appropriate terms from the list on top
quote out half of the "for each" part if necessary (If sh.Type .... End If)

kind regards
Erik