VBA get button clicks other than CommandButton_Click()

Question

Coding in VBA, I have a form window with around multiple input fields and buttons to go with each input field. I want to know if there is a quicker way to code the following

Private Sub deleteButton1_Click()
     MsgBox ("delete button")
End Sub

Private Sub deleteButton2_Click()
     MsgBox ("delete button")
End Sub

Private Sub deleteButton3_Click()
     MsgBox ("delete button")
End Sub

Private Sub deleteButton4_Click()
     MsgBox ("delete button")
End Sub

Im looking for something along the lines of

for i = 1 to 100 
     Private Sub deleteButton+i+_Click()
         MsgBox ("delete button")
     End Sub
next i 

although I am aware that you can't declare functions and subs inside a for loop. Thanks..

Answer 1

Though I agree with Mehow's comment, as a direct answer, you would use a class to handle the events raised by the buttons:

Class cBtnClick:

Public WithEvents btn As msforms.CommandButton

Private Sub btn_Click()
    MsgBox "Delete Button"
End Sub

Userform Code:

Dim BtnCollection As Collection

Private Sub UserForm_Initialize()

    Dim ctl As Object
    Dim clsBtn As cBtnClick

    Set BtnCollection = New Collection

    For Each ctl In Me.Controls
        If TypeOf ctl Is msforms.CommandButton Then
            If UCase(ctl.Caption) = "DELETE" Then
                Set clsBtn = New cBtnClick
                Set clsBtn.btn = ctl
                BtnCollection.Add clsBtn
            End If
        End If
    Next ctl

End Sub

Further to this however, I suggest you look at the structure of your code, you shouldn't really need 17 delete buttons to do the same thing, one should suffice. If each button clears it's respective textbox, then I would consider adding the controls dynamically and encapsulating both the textbox and button in a single class object.

Going from your further comments, have a look at something like this:

Class cInputClear:

Private WithEvents p_tb As MSForms.TextBox
Private WithEvents p_btn As MSForms.CommandButton

Public Sub Add(Parent As Object, top As Long, left As Long, width As Long)
    Set p_tb = Parent.Controls.Add("Forms.Textbox.1")
    With p_tb
        .top = top
        .left = left
        .width = width
    End With

    Set p_btn = Parent.Controls.Add("Forms.CommandButton.1")
    With p_btn
        .top = top
        .left = left + width + 5 '5 for padding
        .Height = p_tb.Height
        .Caption = "Delete"
    End With

End Sub

Private Sub p_btn_Click()
    p_tb.value = ""
End Sub

Public Property Get TextBoxValue() As String
    TextBoxValue = p_tb.value
End Property

Userform:

Private cCollection As Collection

Private Sub UserForm_initialize()

    Dim x As Long
    Dim octrl As cInputClear

    Set cCollection = New Collection
    For x = 1 To 5
        Set octrl = New cInputClear
        octrl.Add Me, 20 * x, 5, 60
        cCollection.Add octrl
    Next x

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

    Dim octrl As cInputClear
    For Each octrl In cCollection
        Debug.Print octrl.TextBoxValue
    Next octrl

End Sub

Suggested approach, re your comment about structure. I think I'd approach it like this if it helps any - this assumes that you only want to edit the description rather than the code as well, but you should get the idea:

N.B - You'll need to put a frame on the userform

Class cData:

Public Code As String
Public Description As String

Class cInputClear:

Option Explicit

Private WithEvents p_tb As MSForms.TextBox
Private WithEvents p_btn As MSForms.CommandButton
Private WithEvents p_lbl As MSForms.Label
Private p_LabelKey As String
Private p_Parent As Object

Public Sub Add(Parent As Object, ByVal LabelKey As String, ByVal TextBoxValue As String, top As Long, left As Long, width As Long)

    Set p_Parent = Parent

    Set p_lbl = Parent.Controls.Add("Forms.label.1")
    With p_lbl
        .top = top
        .left = left
        .Caption = LabelKey
    End With

    Set p_tb = Parent.Controls.Add("Forms.Textbox.1")
    With p_tb
        .top = top
        .left = left + p_lbl.width + 5
        .width = width
        .Value = TextBoxValue
    End With

    Set p_btn = Parent.Controls.Add("Forms.CommandButton.1")
    With p_btn
        .top = top
        .left = left + width + p_lbl.width + 5 '5 for padding
        .Height = p_tb.Height
        .Caption = "Delete"
    End With

    p_LabelKey = LabelKey

End Sub

Private Sub p_btn_Click()
    p_Parent.Parent.Delete Me.LabelKey
End Sub

Public Property Get TextBoxValue() As String
    TextBoxValue = p_tb.Value
End Property

Public Property Get LabelKey() As String
    LabelKey = p_LabelKey
End Property

Private Sub p_tb_Change()
    p_Parent.Parent.Update Me.LabelKey, Me.TextBoxValue
End Sub

UserForm Code:

Option Explicit

Private cCollection As Collection
Private MyCollection As Collection
Private EnableEvents As Boolean

Private Sub Frame1_Click()

End Sub

Private Sub UserForm_initialize()

    Dim x As Long
    Dim myData As cData


    '//////// Just build dummy data for an example
    Set MyCollection = New Collection

    For x = 1 To 5
        Set myData = New cData
        myData.Code = "ABC123" & x
        myData.Description = "Description Of Data"
        MyCollection.Add myData, myData.Code
    Next x

    '///////////////////////////////////////////////


    BindData

End Sub

Private Sub BindData()

    Dim x As Long
    Dim InvoiceItem As cData
    Dim oCtrl As cInputClear

    EnableEvents = False
    Set cCollection = New Collection

    x = 1
    For Each InvoiceItem In MyCollection
        Set oCtrl = New cInputClear

        oCtrl.Add Me.Frame1, _
                    InvoiceItem.Code, _
                    InvoiceItem.Description, _
                    top:=20 * x, _
                    left:=5, _
                    width:=150

        cCollection.Add oCtrl, oCtrl.LabelKey
        x = x + 1
    Next InvoiceItem

    EnableEvents = True

    Me.Frame1.ScrollBars = fmScrollBarsVertical
    Me.Frame1.ScrollHeight = x * 20


End Sub

Public Sub Update(Key As String, ByVal Value As String)
    If EnableEvents Then
        'Update the datasource
       MyCollection(Key).Description = Value
    End If
End Sub

Public Sub Delete(Key As String)

    MyCollection.Remove (Key) 'Update our datasource
    '// Remove all controls from the frame
    Me.Frame1.Controls.Clear
    BindData
End Sub