View Full Version : Deleting two buttons with similar names

11-12-2015, 04:38 PM
I've written a sub that creates two buttons and assigns a different macro to each. When the user clicks either button, I want both buttons to delete.

I'm currently just using this to delete the clicked button:-


The accompanying button I want deleted will have a the same reference number in it's name but different text e.g

Button 1 - hello14
Button 2 - goodbye14

Thanks in advance

11-12-2015, 09:30 PM
How many Buttons are on that sheet?

11-12-2015, 11:55 PM
If the Buttons are Forms Buttons and there are more Buttons, as I assume you have, then this would do.

Sub This_Way_Maybe()
Dim btn As Object
For Each btn In ActiveSheet.Buttons
If btn.Caption = "hello14" Or btn.Caption = "goodbye14" Then btn.Delete
Next btn
End Sub

Or when you create them, name them so you can delete them by name
You could name them "myButt1" and myButt2 for instance.
When you want to delete them simply go

Sub Delete_Buttons()
Dim Shape As Object
For Each Shape In ActiveSheet.Shapes
If Shape.Name = "myButt1" Or Shape.Name = "myButt2" Then Shape.Delete
Next Shape
End Sub

11-13-2015, 02:52 AM
Sorry I don't think I explained that very well

The sub that creates the two buttons is going to be used over to create multiple pairs of buttons, each assigned to the same two macros. I need the line to be generic, which recognises what button is being clicked, deletes it, finds its pair, and deletes that as well.

The only thing the two buttons will have in common will be the number at the end of its name. So I basically need a way to pull that number and find its counterpart with a predetermined prefix

11-13-2015, 06:49 AM
Perhaps something like this. (The first loop can be eliminated if we are sure the number of digits in the suffix, i.e. allow "hello14" or "hello03", but not "hello3" or "hello123")

Dim oneShape as Shape
Dim myIndex as Long
Dim i as long

For i = Len(Application.Caller) to 1 Step -1
If Not(Mid(Application.Caller, i, 1) Like "[0-9]") Then Exit For
Next i

myIndex = mid(Application.Caller, i+1)

For Each oneShape in ActiveSheet.Shapes
If oneShape.Name Like "*" & myIndex Then
End If
Next oneShape

11-13-2015, 07:57 AM
I went with a minute+second+millisecond time stamp as part of the buttons .Name and used that to locate and select the Caller's matching item

Option Explicit
wYear As Integer
wMonth As Integer
wDayOfWeek As Integer
wDay As Integer
wHour As Integer
wMinute As Integer
wSecond As Integer
wMilliseconds As Integer
End Type

Private Declare Sub GetSystemTime Lib "kernel32" (lpSystemTime As SYSTEMTIME)
'ref: http://vbadud.blogspot.com/2008/10/excel-vba-timestamp-milliseconds-using.html#xoFoPraukw5Wg2b3.99

Sub test()
Call MacroAddPair(100, 200, "01")
Call MacroAddPair(300, 400, "02")
End Sub

Sub MacroAddPair(L As Double, T As Double, Marker As String)
Dim sMMSSMMM As String

Call GetSystemTime(eST)
With eST
sMMSSMMM = Format(.wMinute, "00") & Format(.wSecond, "00") & Format(.wMilliseconds, "000")
End With
With ActiveSheet
.Buttons.Add(L, T, 100, 25).Select
With Selection
.OnAction = "MacroToRun"
.Caption = "Hello" & Marker
.Name = "Hello" & "#" & sMMSSMMM
End With

.Buttons.Add(L + 150, T, 100, 25).Select
With Selection
.OnAction = "MacroToRun"
.Caption = "Goodbye" & Marker
.Name = "Goodbye" & "#" & sMMSSMMM
End With
End With

End Sub

Sub MacroToRun()
Dim i As Long
Dim s As String

i = InStr(Application.Caller, "#")
If i = 0 Then Exit Sub
On Error Resume Next
s = Right(Application.Caller, Len(Application.Caller) - i)
ActiveSheet.Buttons("Hello" & "#" & s).Delete
ActiveSheet.Buttons("Goodbye" & "#" & s).Delete
On Error GoTo 0
End Sub

11-15-2015, 08:17 AM
Thanks for both your answers

mike yours works like a charm

Paul I haven't tried yours yet but stamping the buttons that way gets past a problem I was going to have later down the line that I hadn't even thought of yet, so thanks a lot!