CODEWITHSUNDEEP
excelvba
Michi
Michi

Reputation: 5481

Delete button in each sheet using VBA

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?

Upvotes: 0

Views: 1851

Answers (1)

iDevlop
iDevlop

Reputation: 25272

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

Upvotes: 1

Related Questions