Reference to Dynamic Label and Textbox in userform

[hajiali]

Private Sub userform_initialize()
Dim i As Long
number = Worksheets("RESULTS").Range("SS1").Value
Dim LbL1, LbL2, LbL3 As Object
Dim txtB1 As Control
Dim CmB1 As CommandButton
UserForm9.Height = 30 * number + 90
UserForm9.Width = 450
UserForm9.ScrollBars = fmScrollBarsNone

If number > 10 Then
    UserForm9.ScrollBars = fmScrollBarsVertical
    UserForm9.Height = 370
    UserForm9.ScrollHeight = 30 * number + 90
End If

For i = 1 To number
        Set LbL1 = Controls.Add("Forms.Label.1", "Test" & labelCounter, True)
            With LbL1
                .Caption = Sheets("RESULTS").Cells(i, "ST")
                .Name = "Enum" & i
                .Height = 10
                .Left = 30
                .Width = 50
                .Top = 30 * i + 6
                .ForeColor = vbBlack
                .TextAlign = fmTextAlignRight
            End With
        Set LbL2 = Controls.Add("Forms.Label.1", "Test" & labelCounter, True)
            With LbL2
                .Caption = Sheets("RESULTS").Cells(i, "SU")
                .Name = "Ename" & i
                .Height = 10
                .Left = 95
                .Width = 120
                .Top = 30 * i + 6
                .ForeColor = vbBlack
                .TextAlign = fmTextAlignRight
            End With
        Set LbL3 = Controls.Add("Forms.Label.1", "Test" & labelCounter, True)
            With LbL3
                .Caption = Format(Sheets("RESULTS").Cells(i, "SV"), "(###) ###-####")
                .Name = "Phone" & i
                .Height = 10
                .Left = 230
                .Width = 60
                .Top = 30 * i + 6
                .ForeColor = vbBlack
                .TextAlign = fmTextAlignRight
            End With
        Set txtB1 = Controls.Add("Forms.TextBox.1")
            With txtB1
                .Name = "txtBox" & i
                .Height = 20
                .Width = 25
                .Left = 310
                .Top = 20 * i * 1.5
            End With
Next i
 Set CmdBtn = UserForm9.Controls.Add("Forms.CommandButton.1")
    With CmdBtn
        .Caption = "ENTER"
        .Name = "CmdBtn"
        .Left = 290
        .Top = 30 * number + 30
        .Width = 70
    End With
  Controls("txtbox" & 1).SetFocus
End Sub

With the above code Im able to get what I need as whats in the attached picture.

I am need to refer to the Labels and the textboxes of the dynamic userform above.

in the picture the labels are LBL1 and the textboxes to the right are the txtB1

below is what I have but does not seem to work:

Private Sub CmdBtn_Click()
Dim r, i As Long
Dim LbL1, LbL2, LbL3 As Object
Dim txtB1 As Control
With Sheets("DATA").Activate
    For i = 1 To number
        r = Range("E1").Value
        .Cells(r, 5) = LbL1.Enum1.Text
        .Cells(r, 6) = Controls("txtbox" & 1).Value
   Next i
End With
MsgBox "Done", vbOKOnly
End Sub

any help is greatly appreciated

 

[dmt32]

Firstly, Just draw to your attention your variable declarations

Dim r, i As Long

Dim LbL1, LbL2, LbL3 As Object

When you declare multiple variables in a single line Dim statement you must explicitly declare each variables data type otherwise, only the last variable in the line is of the specified data type – all others before it will be variants.

Dim r As Long, i As Long

Dim LbL1 As Object, LbL2 As Object, LbL3 As Object

To get the button added on the fly to work you will need to create an object using withevents then you can assign it to the object with events.

I have made a vain attempt to update your code (and included the WithEvents for the button) but it is just that, you will need to test & adjust as required to meet project need or maybe another here can offer an improved solution for you.

Public WithEvents cmdBtn     As MSForms.CommandButton
Dim Number                   As Long
Dim wsData                   As Worksheet, wsRESULTS  As Worksheet

Private Sub UserForm_Initialize()
    Dim labelCounter    As Long, i As Long, j As Long
    Dim LbL             As Object
    Dim txtB1           As Control
    Dim cmb1            As CommandButton
    
    Set wsRESULTS = ThisWorkbook.Worksheets("RESULTS")
    
    Set wsData = ThisWorkbook.Worksheets("DATA")
    
    Number = Val(wsRESULTS.Range("A1").Value)
    If Number = 0 Then Number = 1
    
    Me.Height = 30 * Number + 90
    Me.Width = 450
    Me.ScrollBars = fmScrollBarsNone
    
    If Number > 10 Then
        Me.ScrollBars = fmScrollBarsVertical
        Me.Height = 370
        Me.ScrollHeight = 30 * Number + 90
    End If
    
    For i = 1 To Number
        For j = 1 To 3
            labelCounter = labelCounter + 1
            Set LbL = Controls.Add("Forms.Label.1", "Test" & labelCounter, True)
            
            With LbL
                .Caption = wsRESULTS.Cells(i, Choose(j, "ST", "SU", "SV")).Text
                .Height = 10
                .Left = Choose(j, 30, 95, 230)
                .Width = Choose(j, 50, 120, 60)
                .Top = 30 * i + 6
                .ForeColor = vbBlack
                .TextAlign = fmTextAlignRight
            End With
            
            Set LbL = Nothing
        Next j
        
        Set txtB1 = Controls.Add("Forms.TextBox.1")
        With txtB1
            .Name = "txtBox" & i
            .Height = 20
            .Width = 25
            .Left = 310
            .Top = 20 * i * 1.5
        End With
        Set txtB1 = Nothing
    Next i
    
    Set cmdBtn = Me.Controls.Add("Forms.CommandButton.1")
    With cmdBtn
        .Caption = "ENTER"
        .Name = "CmdBtn"
        .Left = 290
        .Top = 30 * Number + 30
        .Width = 70
    End With
    Controls("txtbox" & 1).SetFocus
End Sub

Private Sub CmdBtn_Click()
    Dim r As Long, i As Long
    
    With wsData
        r = .Range("E1").Value
        For i = 1 To Number
            .Cells(r, 5) = Me.Controls("Test" & i).Caption
            .Cells(r, 6) = Me.Controls("txtbox" & i).Value
            r = r + 1
        Next i
    End With
    
    MsgBox "Done", vbOKOnly, "All Done"
End Sub

Hope Helpful

Dave

 

[hajiali]

thank you dmt32 for that information and help. I will give that a try on Friday when I’m back on my computer. I will let you know the outcome.


Thanks