Delete button in each sheet using VBA

Question

I use the following VBA to insert a button in each sheet:

Sub Insert_Button_in_each_sheet()
Dim b As Worksheet
For Each b In Worksheets
    b.Select
    Dim Button_01 As Button
    Set Button_01 = b.Buttons.Add(423.75, 0, 48, 15)
    Set Range_Button_01 = b.Range("B1:C5")
    Selection.Name = "Button_01"
    With Button_01
    .Top = Range_Button_01.Top
    .Left = Range_Button_01.Left
    .Width = Range_Button_01.Width
    .Height = Range_Button_01.Height
    .Text = "Button_01"
    End With
Next b
End Sub

All this works fine.

Now, I want to delete this button in each sheet using this VBA:

Sub Delete_Existing_Button()
Dim b As Worksheet
For Each b In Worksheets
    b.Select
    b.Shapes("Button_01").Delete
Next b
End Sub

However, when I run this code I get runtime error 5.

What do I need to change in my code to make it work?

Try adding Button_01.Name = "Button_01" to your Insert code. See if the delete code works then — chris neilsen
– chris neilsen, Commented Sep 5, 2019 at 7:36
What makes you think your button has that name ? SelectionName =... never use selectionexcept for a good reason (and they are very rare) — Patrick Honorez
– Patrick Honorez, Commented Sep 5, 2019 at 7:43
You are naming the worksheet instead of the button. That's why you should avoid using .select and .selection and use explicit references to your objects instead. Furthermore, if you want your code to be consistent, since you used the .buttons collection in the first sub, you should use it in the second one as well instead of using the .shapes collection. It will work both ways but for the sake of consistency I would use only one of two. — Stavros Jon
– Stavros Jon, Commented Sep 5, 2019 at 7:45
Thanks a lot for all your additional infos regarding the select method. — Michi
– Michi, Commented Sep 5, 2019 at 7:48

Michi · Accepted Answer · 2019-09-05 08:21:36Z

1

Option A: Add the following line to the insert code:

Button_01.Name = "Button_01"

Option B: Change the delete code to the following:

For Each b In Worksheets
    b.buttons.Delete
    'or
    'b.buttons(1).delete
Next b

edited Sep 5, 2019 at 8:21

Michi

5,56510 gold badges59 silver badges125 bronze badges

answered Sep 5, 2019 at 7:32

Patrick Honorez

25.4k12 gold badges97 silver badges163 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Delete button in each sheet using VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related