Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete button control using VBA (2002/SP3)

    I get an error in line 4 of this code. What's wrong with this code?

    Public Sub DeleteButtonControl()
    Dim shp As Shape
    For Each shp In ActiveSheet.Shapes
    If shp.FormControlType = xlButtonControl Then ' << Error occurs here: Run-time error '1004'
    shp.Delete
    End If
    Next shp
    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete button control using VBA (2002/SP3)

    FormControlType is only valid for Form controls, so you have to check first whether the shape is a Form control.
    Also, when deleting items from a collection, it's best to loop backwards.

    Public Sub DeleteButtonControl()
    Dim i As Integer
    For i = ActiveSheet.Shapes.Count To 1 Step -1
    If ActiveSheet.Shapes(i).Type = msoFormControl Then
    If ActiveSheet.Shapes(i).FormControlType = xlButtonControl Then
    ActiveSheet.Shapes(i).Delete
    End If
    End If
    Next i
    End Sub

  3. #3
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete button control using VBA (2002/SP3)

    Thanks alot Hans. Works great.

    Can you explain to me though why the original code with the statement "If shp.FormControlType = xlButtonControl Then" didn't work?
    Also, you wrote in your response that "when deleting items from a collection, it's best to loop backwards." Why is that?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete button control using VBA (2002/SP3)

    The line

    If shp.FormControlType = xlButtonControl Then

    fails if the shape is not a forms control. The FormControlType property is only valid for forms controls, not for all shapes. So we first check the Type property to see whether the shape is a forms control, and only if so, check the FormControlType property.

    About the loop: let's look at a very simple example: the sheet contains three shapes of which the second one is a control button.

    The code using For Each shp In ActiveSheet.Shapes will look at the first shape, skip it because it isn't a button.
    It then moves on to the second shape and deletes it because it is a button.
    It then tries to look at the third shape, but there is no third shape any more: because we deleted the second shape, the third one has by now become the second one.
    So you get an error message.

    The code using For i = ActiveSheet.Shapes.Count To 1 Step -1 starts by looking at the third shape, and skips it.
    It then moves on to the second shape and deletes it.
    Finally, it looks at the first shape (which is still there) and skips it.

    Note: some collections handle this problem gracefully if you use For Each, but most of them don't. So it's safest to loop backwards.

  5. #5
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete button control using VBA (2002/SP3)

    Great explanation.

    One more question. The forms control is the one that you get from the "Forms" toolbar, not the "Control Toolbox" toolbar right?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete button control using VBA (2002/SP3)

    Yes, that is correct.

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Delete button control using VBA (2002/SP3)

    And if you are using buttons from the Forms toolbar, you can loop the Buttons collection directly.
    FWIW.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •