Creating controls on userform on the fly with VBA

[Waimea]

I am trying to create a dynamic number of labels and textboxes with the following code and it sort of works.

However, I want to use a variable to make sure that the labels and the textboxes don't go outside of the userform.

I have tried a select case and if else statements but they offset the labels/textboxes in the wrong way.

Ideally, I would like to have 3 or 4 columns with pairs of labels/textboxes. With 25 to 35 pairs in each column.

Code for the labels:

Private Sub CommandButton2_Click()
Dim i As Long
Dim number As Integer
Dim productID As Variant
Dim lastrow
Dim j As Integer
Dim labelcounter As Integer
Dim myvalue As Variant
Dim thelabel As Variant


Dim ws As Worksheet
Set ws = Sheets("Sheet1")


lastrow = ws.Cells(Rows.Count, 1).End(xlUp).row
number = lastrow


Dim txtbox As Control


For i = 1 To lastrow
Set txtbox = UserForm3.Controls.Add("Forms.Label.1")


productID = Sheet1.Range("A" & i + 2).Value


With txtbox
    .Name = "textbox" & i
    .Height = 18
    .Width = 100
    .Left = 10
    .Caption = productID
    .Top = 20 * i *  1
End With
Next i


End Sub

Code for the textboxes:

Private Sub CommandButton3_Click()
Dim i As Long
Dim number As Integer
Dim productID As Variant
Dim lastrow
Dim j As Integer
Dim labelcounter As Integer
Dim myvalue As Variant
Dim thelabel As Variant


Dim ws As Worksheet
Set ws = Sheets("Sheet1")


lastrow = ws.Cells(Rows.Count, 1).End(xlUp).row
number = lastrow


Dim txtbox As Control


For i = 1 To lastrow
Set txtbox = UserForm3.Controls.Add("Forms.TextBox.1")
 
productID = Sheet1.Range("D" & i + 2).Value


With txtbox
    .Name = "textbox" & i
    .Height = 18
    .Width = 100
    .Left = 125
    .Value = productID
    .Top = 20 * i * 1
    .TabIndex = i
End With
Next i


End Sub

I am glad for all suggestions!

 

[Akuini]

I answered a similar problem in this thread:

https://www.mrexcel.com/forum/excel...etween-eachother-post5179941.html#post5179941

see if it helps

 

[Waimea]

Hi Akuini,

thank you for your reply! Your answer helped me!

 

[Waimea]

.Value = Sheet1.Cells(i, 4).Value

How can I get a value from a listbox into the value of the textbox or the caption of the label?

 

[Akuini]

For a single column Listbox:

With ListBox1
For i = 0 To .ListCount - 1
Me.Controls("txtBox" & i + 1).Value = .List(i)
Next
End With

for a multi column Listbox (for first column):

Me.Controls("txtBox" & i + 1).Value = .List(i, 0)

Note: in Listbox, row & column starts at 0

 

[Waimea]

Hi Akuini,

thank you for your reply! I got it to work with your code!

 

[Akuini]

You're welcome, glad to help, & thanks for the feedback.