How to use a dynamic button in Userform VBA

Question

I could really use some help on this. I've read through about 60+ websites and it's either not clicking (pun intended), or it's incorrect for my application. Here's the rundown:

Goal: Use a "Submit" button that was dynamically created in a Userform to copy the Caption from an OptionButton to a dynamic cell on the worksheet, and then clear/close the Userform.

Background: The userform is called from a change in a column in the worksheet. Here's a snippet of the code used to call the userform:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim lastRow As Long

    With Worksheets("Test")
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    With Target
        If .Count > 1 Then Exit Sub
        If Not Intersect(Range("B1:B" & lastRow), .Cells) Is Nothing Then
            Application.EnableEvents = False
            If IsEmpty(.Value) Then
                .Offset(0, 1).ClearContents
            Else
                With .Offset(0, 1)
                    .NumberFormat = "mmm dd yyyy hh:mm:ss"
                    .Value = Now
                    UserForm1.Show
                End With
            End If
            Application.EnableEvents = True
        End If
    End With

End Sub

After the Userform is shown, it initializes. It pulls from a list on the spreadsheet to populate how many option buttons there are, their captions, and the dimensions of each item on the Userform. The code for that is this:

Sub UserForm_Initialize()

Dim HLastRow As Integer
Dim NoOfExplanations As Integer
Dim TopPixels As Integer
Dim UserFormHeight As Integer
Dim UserFormWidth As Integer
Dim Opt As Variant
Dim i As Integer
Dim ExplanationRow As Integer
Dim lbl As MSForms.Label
Dim LabelCap As String
Dim btn As CommandButton
Dim OtherInput As MSForms.TextBox
Dim Margins As Integer

    With Worksheets("Test")
        HLastRow = .Cells(.Rows.Count, "H").End(xlUp).Row
    End With

NoOfExplanations = Application.WorksheetFunction.CountA(Worksheets("Test").Range("H2:H" & HLastRow))
Margins = 20

LabelCap = "You have chosen a non sequential row for your team/subteam. Please select an explanation below before you are able to proceed"
UserFormWidth = Len(LabelCap) * 2
TopPixels = (18 * 2)
UserFormHeight = TopPixels + 80 + (20 * NoOfExplanations)

    With UserForm1
        .Width = UserFormWidth + 40
        .Height = UserFormHeight
    End With

    Set lbl = UserForm1.Controls.Add("Forms.Label.1")
    With lbl
        .Top = 10
        .Left = 20
        .Height = 20
        .Width = UserFormWidth - 20
        .Caption = LabelCap
    End With

ExplanationRow = 2
For i = 1 To NoOfExplanations

    Set Opt = UserForm1.Controls.Add("Forms.OptionButton.1", "OptionButton" & i, True)

    Opt.Caption = Worksheets("Test").Cells(ExplanationRow, 8).Value

    If Worksheets("Test").Cells(ExplanationRow, 8).Value = "Other" Then
        Set OtherInput = UserForm1.Controls.Add("Forms.TextBox.1")
        With OtherInput
            .Top = TopPixels
            .Width = UserFormWidth - (Len(Worksheets("Test").Cells(ExplanationRow, 8).Value) * 11)
            .Left = UserFormWidth - (UserFormWidth - (Len(Worksheets("Test").Cells(ExplanationRow, 8).Value) * 11))
            .Height = 18
        End With
    End If

    If Len(Worksheets("Test").Cells(ExplanationRow, 8).Value) > 45 Then
        Opt.Width = UserFormWidth - 10
        Opt.Height = 36
        Opt.Left = 18

        Opt.Top = TopPixels
        TopPixels = TopPixels + 38
    End If

    If Len(Worksheets("Test").Cells(ExplanationRow, 8).Value) <= 45 Then
        Opt.Width = UserFormWidth - 10
        Opt.Height = 18
        Opt.Left = 18

        Opt.Top = TopPixels
        TopPixels = TopPixels + 20
    End If
    ExplanationRow = ExplanationRow + 1
Next i

    Set btn = UserForm1.Controls.Add("Forms.CommandButton.1")
    With btn
        .Top = TopPixels
        .Width = 40
        .Left = ((UserFormWidth + 40) / 2) - 20
        .Height = 20
        .Caption = "Submit"
        .Name = btn
    End With
End Sub

Question: So, how do I get the btn created here in the Userform to both copy the selected OptionButton caption to the dynamic cell, and then clear/close the Userform?

I know it's a stretch, but I'm trying to fill in the cell that is two columns over from the "Target" cell that triggers the Userform to open. The code fills in the current date/time in the .Offset(0, 1) in the Worksheet_Change snipped, but is there a way to place the OptionButton caption in the cell at .Offset(0, 2)?

I'm still pretty new to VBA and this one thing is really sticking a thorn in me.

I'll be incredibly grateful for any help on this.

Thanks! Joe

Answer 1

Changing your btn variable to a class level variable and using WithEvents will allow you to access the dynamic buttons events.

Private WithEvents btn As CommandButton

Private Sub btn_Click()
    Dim ctrl As Control
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "OptionButton" Then
            If ctrl.Object.Value Then
                MsgBox ctrl.Object.Caption
            End If
        End If
    Next
End Sub