0

How to make the relatively same event code to multiple generated userform controls?

I have made a userform pre-start that creates command buttons off a list from another sheet after initialize. The idea right now is to put the stored caption of the userform control I click on the active cell. Right now I can only add a event sub on the one that is already on the userform. How can I add the same event code that I applied to every command button that is generated? Here is the code so far, including the click event sub:

Option Explicit

Dim Genre(0 To 20) As msforms.CommandButton

Private Sub UserForm_Initialize()
    Dim i As Integer
    Dim Genre(0 To 20) As msforms.CommandButton
    i = 0
    
    Do
        Set Genre(i) = Me.Controls.Add("Forms.CommandButton.1")
        With Genre(i)
                .Width = Me.Controls("Unknown").Width
                .Height = Me.Controls("Unknown").Height
                .Left = Me.Controls("Unknown").Left
                .Top = Me.Controls("Unknown").Top + Me.Controls("Unknown").Height * (i + 1)
                .Caption = Worksheets("Button Data Sheet").Range("A1").Offset(i, 0).Value
        End With
        i = i + 1
    Loop Until Worksheets("Button Data Sheet").Range("A1").Offset(i, 0).Value = ""
    
End Sub

Private Sub Unknown_Click()
    ActiveCell.Value = Me.Unknown.Caption
End Sub

Thank you very much!

EDIT followup question. Thank you chris nielsen for the even class module. But now I have trouble trying to refer which command button did I press as I need the button's caption to put on the active cell. I cannot use Userform1. because the command button is not generated yet.

Here is the code for the userform.

Option Explicit

Dim GenreArray() As New Class1
Dim Genre(0 To 20) As MSForms.CommandButton

Private Sub UserForm_Initialize()
    Dim i As Integer
    i = 0
    Do
        Set Genre(i) = Me.Controls.Add("Forms.CommandButton.1")
        With Genre(i)
                .Width = Me.Controls("Unknown").Width
                .Height = Me.Controls("Unknown").Height
                .Left = Me.Controls("Unknown").Left
                .Top = Me.Controls("Unknown").Top + Me.Controls("Unknown").Height * (i + 1)
                .Caption = Worksheets("Button Data Sheet").Range("A1").Offset(i, 0).Value
        End With
        
        ReDim Preserve GenreArray(0 To i)
        Set GenreArray(i).clickEvent = Genre(i)
        i = i + 1
    Loop Until Worksheets("Button Data Sheet").Range("A1").Offset(i, 0).Value = ""
    
End Sub

Private Sub Unknown_Click()
    ActiveCell.Value = Me.Unknown.Caption
End Sub

Here is the code for the class module.

Option Explicit

Public WithEvents clickEvent As MSForms.CommandButton

Private Sub clickEvent_click()

    ActiveCell.Value = UserForm1.Unknown.Caption

End Sub
dropkick
  • 3
  • 2
  • Be sure to use the [event class method](https://stackoverflow.com/a/8986622) – chris neilsen Feb 15 '22 at 02:17
  • @chrisneilsen Thank you but I have a followup question. Now that it has successfully go refer to the class module sub, how can I make the class module refer to the button' caption that I clicked? the Me. does not work anymore and I realized that I need to make that script dynamic as well. Off the event class module link you sent, it only has a generic msgbox command that won't refer to the command button clicked. – dropkick Feb 15 '22 at 03:11
  • In the event class the object `clickEvent` is the button that was clicked. In this context it has a limited set of properties. Use `Private Sub clickEvent_click() ActiveCell.Value = clickEvent.Caption End Sub` – chris neilsen Feb 15 '22 at 03:19

0 Answers0