VBA Code:
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:
VBA Code:
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