Creating controls on userform on the fly with VBA

Waimea

Active Member
Joined
Jun 30, 2018
Messages
372
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:

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

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

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Waimea

Active Member
Joined
Jun 30, 2018
Messages
372
Code:
.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

Well-known Member
Joined
Feb 1, 2016
Messages
2,575
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

For a single column Listbox:

Code:
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):
Code:
Me.Controls("txtBox" & i + 1).Value = .List(i, 0)

Note: in Listbox, row & column starts at 0
 

Waimea

Active Member
Joined
Jun 30, 2018
Messages
372
Hi Akuini,

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

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,575
Office Version
  1. 365
Platform
  1. Windows
You're welcome, glad to help, & thanks for the feedback.:)
 

Watch MrExcel Video

Forum statistics

Threads
1,108,814
Messages
5,525,050
Members
409,617
Latest member
Lenaf

This Week's Hot Topics

Top