Martin has attended:
Excel VBA Introduction course
Excel VBA Intermediate course
Excel VBA Advanced course
Userforms and Dynamically Created Text boxes
Hi,
I have created a userform that has a checkbox. If the checkbox is checked then the code automatically generates a new label and three new textboxes. How do I access the data in the dynamically created text boxes?
userform name = frmhandling
checkbox = modstandard
code below
Thank you
Martin
Private Sub CommandButton1_Click()
Load Me
Sheets("Info").Select
Worksheets("Info").Cells(1, 2) = frmHandling.TextBox1.Text
Worksheets("Info").Cells(2, 2) = frmHandling.TextBox2.Text
Worksheets("Info").Cells(3, 2) = frmHandling.TextBox3.Text
Worksheets("Info").Cells(4, 2) = frmHandling.TextBox4.Text
Worksheets("Info").Cells(5, 2) = frmHandling.TextBox5.Text
Worksheets("Info").Cells(6, 2) = frmHandling.TextBox7.Text
Worksheets("Info").Cells(6, 3) = frmHandling.TextBox8.Text
Worksheets("Info").Cells(6, 4) = frmHandling.TextBox9.Text
Worksheets("Info").Cells(6, 5) = frmHandling.TextBox10.Text
Worksheets("Info").Cells(6, 6) = frmHandling.TextBox11.Text
Worksheets("Info").Cells(6, 7) = frmHandling.TextBox12.Text
Worksheets("Info").Cells(7, 2) = frmHandling.TextBox16.Text
Worksheets("Info").Cells(7, 3) = frmHandling.TextBox17.Text
Worksheets("Info").Cells(7, 4) = frmHandling.TextBox18.Text
Worksheets("Info").Cells(7, 5) = frmHandling.TextBox19.Text
Worksheets("Info").Cells(7, 6) = frmHandling.TextBox20.Text
Worksheets("Info").Cells(7, 7) = frmHandling.TextBox21.Text
Worksheets("Info").Cells(8, 2) = frmHandling.TextBox25.Text
Unload Me
End Sub
Private Sub CommandButton2_Click()
ActiveWindow.Close
ActiveWindow.Close
ActiveWindow.Close
ActiveWindow.Close
End Sub
Private Sub ModStandard_Click()
If frmHandling.ModStandard.value = True Then
AO629 = "True"
Call Add_Dynamic_label
Call Add_Dynamic_textbox1
Call Add_Dynamic_textbox2
Call Add_Dynamic_textbox3
Else
AO629 = "False"
End If
End Sub
Sub Add_Dynamic_label()
'Add Dynamic TextBox and assign it to object 'Lbl'
Set lbl = frmHandling.Controls.Add("Forms.label.1")
'Assign TextBox Name
lbl.Caption = "Max Idle Max (e.g. 16, 17,18)"
'TextBox Border Style
lbl.BorderStyle = 0
'TextBox Position
lbl.Left = 24
lbl.Top = 288
lbl.TextAlign = 1
lbl.Font.Bold = 1
lbl.Font.Size = 11
lbl.BackColor = &H80000018
lbl.Width = 174
lbl.ForeColor = &H0&
End Sub
Sub Add_Dynamic_textbox1()
'Add Dynamic TextBox and assign it to object 'Lbl'
Set lbl = frmHandling.Controls.Add("Forms.textbox.1", Name:="MaxIdleMax1")
'Assign TextBox Name
'lbl.Name = "MaxIdleMax1"
lbl.AutoSize = True
lbl.BackStyle = 1
'TextBox Border Style
lbl.BorderStyle = 0
'TextBox Position
lbl.Left = 222
lbl.Top = 288
lbl.TextAlign = 1
lbl.Font.Bold = 1
lbl.Font.Size = 11
lbl.BackColor = &H80000005
lbl.Width = 18.75
lbl.Height = 18
lbl.ForeColor = &H80000008
End Sub
Sub Add_Dynamic_textbox2()
'Add Dynamic TextBox and assign it to object 'Lbl'
Set lbl = frmHandling.Controls.Add("Forms.textbox.1")
'Assign TextBox Name
lbl.Name = "MaxIdleMax2"
lbl.AutoSize = True
lbl.BackStyle = 1
'TextBox Border Style
lbl.BorderStyle = 0
'TextBox Position
lbl.Left = 252
lbl.Top = 288
lbl.TextAlign = 1
lbl.Font.Bold = 1
lbl.Font.Size = 11
lbl.BackColor = &H80000005
lbl.Width = 18.75
lbl.Height = 18
lbl.ForeColor = &H80000008
End Sub
Sub Add_Dynamic_textbox3()
'Add Dynamic TextBox and assign it to object 'Lbl'
Set lbl = frmHandling.Controls.Add("Forms.textbox.1")
'Assign TextBox Name
lbl.Name = "MaxIdleMax3"
lbl.AutoSize = True
lbl.BackStyle = 1
'TextBox Border Style
lbl.BorderStyle = 0
'TextBox Position
lbl.Left = 282
lbl.Top = 288
lbl.TextAlign = 1
lbl.Font.Bold = 1
lbl.Font.Size = 11
lbl.BackColor = &H80000005
lbl.Width = 18.75
lbl.Height = 18
lbl.ForeColor = &H80000008
End Sub
RE: Userforms and Dynamically Created Text boxes
Hi Martin,
Thank you for the forum question.
You access the data in a textbox by the name of the textbox.
MyVariable=frmhandling.frmhandling.Text
If you want to get the content of the textbox in a variable
Or if you want to get the content of a Textbox in a cell
Range("a1").text=frmhandling.frmhandling.Text
If the content of a textbox is a number
Range("a1").value=frmhandling.frmhandling.value
Please let me know if it is not what you are looking for.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Userforms and Dynamically Created Text boxes
Hi Martin,
Thank you for the forum question.
You access the data in a textbox by the name of the textbox.
MyVariable=frmhandling.frmhandling.Text
If you want to get the content of the textbox in a variable
Or if you want to get the content of a Textbox in a cell
Range("a1").text=frmhandling.frmhandling.Text
If the content of a textbox is a number
Range("a1").value=frmhandling.frmhandling.value
Please let me know if it is not what you are looking for.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector