Thread: Delete Form Control Buttons in a range on worksheet

I am trying to delete the form the control buttons in a specified range in a sheet. But the code i have written is returning error:

Run Time Error '438':
Object Doesn't support this property or method.

the code i have written is.

Private Sub ComboBox1_Change()

Dim shp As Shape

If ComboBox1.Value = "IONE" Then
    Application.Goto Reference:="ionee"
    Selection.Copy
    Range("A6").Select
    ActiveSheet.Paste
ElseIf ComboBox1.Value = "PLS" Then

Else
'MsgBox("Please select an option from drop down..!", vbOKOnly, "Error") = vbOK
Range("A6:Y54").Select
Selection.ClearContents
 'Range("D8:D14").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
        :=xlBetween
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    DBC
   End If

End Sub
Public Sub DBC()
Dim i As Integer
For i = ActiveSheet.Range("A6:Y54").Shapes.Count To 1 Step -1
If ActiveSheet.Range("A6:Y54").Shapes(i).Type = msoFormControl Then
If ActiveSheet.Range("A6:Y54").Shapes(i).FormControlType = xlButtonControl Then
ActiveSheet.Range("A6:Y54").Shapes(i).Delete
End If
End If
Next i
End Sub

Any help will be appreciated. Thanks.!!

I believe this is the offending line.
.Shapes is a proprety of a Sheet object, not a Range object. Perhaps

For i = ActiveSheet.Range("A6:Y54").Shapes.Count To 1 Step -1

Perhaps,

Dim ClearRange As Range

Set ClearRange = ActiveSheet.Range("A6:Y54")

With ClearRange.Parent
    For i = 1 to .Shapes.Count
        With .Shapes(i)
            If Not Application.Intersect(Range(.TopLeftCell, .BottomRightCell), ClearRange) Is Nothing Then
                If .Type = msoFormControl Then
                    If .FormControlType = xlButtonControl Then
                        .Delete
                    End IF
                End If
            End If
        End With
    Next i
End With

Hi,
I suspect that with Mike's code you may need to loop backwards

Dim ClearRange As Range

Set ClearRange = ActiveSheet.Range("A6:Y54")

With ClearRange.Parent
    For i = .Shapes.Count to 1 step -1
        With .Shapes(i)
            If Not Application.Intersect(Range(.TopLeftCell, .BottomRightCell), ClearRange) Is Nothing Then
                If .Type = msoFormControl Then
                    If .FormControlType = xlButtonControl Then
                        .Delete
                    End IF
                End If
            End If
        End With
    Next i
End With

@ mikerickson,

I had a look at this Thread earlier but could not think of how to answer it.

Your code looks perfect and I can't see anything wrong with it. Nice work!

Really you should reference buttons rather than shapes, makes things faster:

Dim ClearRange As Range
Dim i As Integer
Set ClearRange = ActiveSheet.Range("A6:Y54")

With ClearRange.Parent
    For i = .Buttons.Count To 1 Step -1
        With .Buttons(i)
            If Not Application.Intersect(Range(.TopLeftCell, .BottomRightCell), ClearRange) Is Nothing Then .Delete
        End With
    Next i
End With

Are you certain they are forms control buttons and not ActiveX ones?

@onError Microsoft usefully decided to hide forms controls from intellisense, they are still there you just have to show hidden objects

No problem, thanks for the rep

Originally Posted by Kyle123

@onError Microsoft usefully decided to hide forms controls from intellisense, they are still there you just have to show hidden objects

Thanks, but I know that (I use them all the time because I hate ActiveX).
I just could not, and still cannot, see why Mike's version would not work given the tweak to step backwards. It should do exactly the same thing as your version.

haha me too, I avoid ActiveX controls like the plague

I agree and it does work fine for me when stepping backwards, I too have no idea why it doesn't work for the OP