How to get the ID of a dynamic button on a function?

Question

I've been trying to get the ID of a dynamically created button on a function which is called when any of the dynamic buttons is clicked.

The program is pretty simple, is a checkout for an ice cream store, the buttons get their values from a list of products on a Sheet, the only thing that I need is to get the name of the button to get the index in which is located, so I can read the products properties (such as price)

I am currently using a UserForm, which I modified from this post, but basically does the same:

Assign code to a button created dynamically

I also tried with dynamic buttons on a Sheet, but couldn't get to run the function, It is supposed to run with the following:

Set btn = Sheet1.Buttons.Add(t.Left, t.Top, t.Width, t.Height)
With btn
    .OnAction = "Button_Click"
    .Caption = Product(idx)
    .Name = Product(idx) & " " & i
End With

And I'm pretty sure that this would work within the "Button_Click" Sub

Application.Caller

Any help would be much appreciated, although I'd rather use an UserForm, since it looks better

PS. I don't want to hide/show buttons to solve this issue, that would become a potential limit on the number of items that can be read, and also, every item would be kind of permanently fixed

score 0 · Accepted Answer · answered Feb 09 '17 at 06:30

Create a Class Module named "cCheckBox" for the CheckBox and use the code below.

cCheckBox Class Module Code

Option Explicit

Public WithEvents CheckBoxEvents As MSForms.CheckBox

Private Sub CheckBoxEvents_click()

    Dim CbIndex As Long

    CbIndex = CInt(Mid(CheckBoxEvents.Name, 3)) ' get the CheckBox Index pressed
    MsgBox "CheckBox Index selected is " & CbIndex ' display in a msgbox

End Sub

The code for dynamically adding CheckBoxes to a User_Form (in the UserForm_Initialize event) is :

Option Explicit

' ===== Variables and Objects decleration section ===== 
Dim TchkBox                          As MSForms.CheckBox
Dim CheckBoxArray()                  As New cCheckBox


Private Sub UserForm_Initialize()

Dim i As Long

For i = 1 To 5 ' create 5 dynamic Check Boxes in User_Form
    Set TchkBox = Me.Controls.Add("Forms.CheckBox.1", "Cb" & i, True) ' create a checkbox in user_form

    With TchkBox ' modify checkbox size and placement
        .Width = 180
        .Height = 18
        .Left = 40
        .Top = 54 + 24 * i

        ReDim Preserve CheckBoxArray(0 To i) ' add another CheckBox to array
        Set CheckBoxArray(i).CheckBoxEvents = TchkBox
    End With
Next i

End Sub

How to get the ID of a dynamic button on a function?

1 Answers1