Get and write a value to a sheet from a textbox created dynamically in a UserForm

Question

I have a UserForm composed of a TextBox and a CommandButton.

By entering a value in the TextBox, for example 3, and clicking the CommandButton, I create three new TextBox with labels linked to each one of them. It also creates just under those TextBox and Labels another CommandButton.

I write in each new TextBox a value. For example in the first TextBox I write "Banana", in the second "Apple" and so on.

I want by clicking the new CommandButton, to get the value from the new TextBoxes and write them in a sheet.

How do I get the value from these new TextBoxes considering they are created during the runtine.

Here is the code linked to my UserForm:

Option Explicit
Dim cmdArray() As New Classe1

Public Sub nbEquipButtonValidation_Click()

     Dim i As Variant
     Dim Nb_equip As Integer
     Dim j As Long
     
     Nb_equip = UserForm1.nbEquipTextBox.Value
     
     For i = 1 To Nb_equip
        
        Dim EquipLabel
        Dim Text_Boxes
        Dim CmdBtn

        Set EquipLabel = Me.Controls.Add("Forms.Label.1")
        
        With EquipLabel
            .Top = 25 + 10 * i * 2
            .Left = 10
            .Caption = "Equipement n°" & CStr(i)
            .Name = "Equip" & CStr(i)
        End With
        
        Set Text_Boxes = Me.Controls.Add("Forms.TextBox.1", True)
        
        With Text_Boxes
            .Top = 20 + 10 * i * 2.1
            .Left = 100
            .Name = "Text_Box" & CStr(i)
        End With

    Next i
    
    Set CmdBtn = Me.Controls.Add("Forms.CommandButton.1")
    With CmdBtn
        .Top = 20 + 10 * Nb_equip * 2.1 + 30
        .Left = 75
        .Caption = "Créer"
        .Name = "Validation"
    End With
    
    ' Apply a class to the new Button
    j = 1
    
    ReDim Preserve cmdArray(1 To j)
    Set cmdArray(j).CmdEvents = CmdBtn

    Set CmdBtn = Nothing

End Sub

Here is the class I created to get these data and write them in a sheet by clicking the second CommandButton.

Option Explicit

Public WithEvents CmdEvents As MSForms.CommandButton

Private Sub CmdEvents_Click()

Dim Ws As Worksheet

Set Ws = Worksheets("Sheet1")

Dim i As Variant
Dim Nb_equip As Integer

Nb_equip = UserForm1.nbEquipTextBox.Value
      
For i = 1 To Nb_equip
       
    With Ws
        
        .Cells(6, 2 + i * 2).Value = "Exp" & CStr(i)
    
    End With

Next i

End Sub

In my loop I want to replace "Exp" & CStr(i) by the name of the value in the TextBox according to the position i of creation.

Answer 1

This should work:

Modify your class to add a reference to the Form object:

Option Explicit

Public WithEvents CmdEvents As MSForms.CommandButton
Public frm As Object  '<<<<<<<<<<<

Private Sub CmdEvents_Click()

    Dim Ws As Worksheet
    Set Ws = Worksheets("Sheet1")

    Dim i As Variant
    Dim Nb_equip As Integer

    Nb_equip = frm.nbEquipTextBox.Value

    For i = 1 To Nb_equip
        Ws.Cells(6, 2 + i * 2).Value = frm.Controls("Exp" & CStr(i)).Text
    Next i

End Sub

Then add the line below:

Set cmdArray(j).CmdEvents = CmdBtn
Set cmdArray(j).frm = Me  '<<<<<<<<