Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

userforms and dynamically create

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Userforms and Dynamically Created Text boxes

Userforms and Dynamically Created Text boxes

ResolvedVersion 2007

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

Sat 6 Feb 2021: Automatically marked as resolved.

 

Training courses

Training information:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.

Excel tip:

Validating text entries

1. Select the range of cells.
2. From the Data menu, select Validation.
3. Select the Settings tab.
4. From the Allow dropdown list, select Custom.
5. In the Formula box, enter the following formula:

=IsText (A1)

where A1 is the first cell in the range.
6. Click OK.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.28 secs.