VBA userform multiple textbox

[jeelpuranik]

Hello Everyone,
I am actually working on a project and i need help with something.
I have to create multiple textbox dynamically and ask user to enter some values in that field and then use that value in my module(where i have written rest of the code for the program)

So i have successfully managed to dynamically create textbox as i desire but now i want to use the value that user enters in the textbox and assign it to variable in my module.

My code for dynamically creating textbox.


Private Sub Userform_Initialize()
Dim i As Long
number = InputBox("Enter the number of floor", "Enter Floor Number")
Me.StartUpPosition = 0
Me.ScrollHeight = number * 21
Dim TxtB1 As Control
For i = 1 To number
Set TxtB1 = Controls.Add("Forms.TextBox.1")
With TxtB1
.Name = "TxtBox" & I
.Height = 20
.Width = 100
.Left = 20
.Top = 20 * i * 1
End With
Next i
End SubCan You please help me with the following.

[p45cal]

Make Number global (at least to the userform) by Dimming it at the top of the module, outside all subs.
Then you can use that again in say a click event of a button (code at bottom)
Dim Number As Long

Private Sub Userform_Initialize()
Dim i As Long, TxtB1 As Control

Number = InputBox("Enter the number of floor", "Enter Floor Number")
Me.StartUpPosition = 0
Me.ScrollHeight = Number * 21
For i = 1 To Number
Set TxtB1 = Controls.Add("Forms.TextBox.1")
With TxtB1
.Name = "TxtBox" & i
.Height = 20
.Width = 100
.Left = 20
.Top = 20 * i * 1
End With
Next i
End Sub

Private Sub CommandButton1_Click()
ReDim myVariable(1 To Number)
For i = 1 To Number
myVariable(i) = Controls("TxtBox" & i).Value 'assign to variable
Sheet2.Cells(i + 10, "Q").Value = Controls("TxtBox" & i).Value 'and/or write to sheet
Next i
End Sub

27839

[SamT]

As your code reads; Floor 1 has one textbox, floor 2 has 2 TextBoxes, floor 3 has 3, and so on.
How do Users know what to enter in the Textboxes? We usually add Labels next to TextBoxes to indicate their usage.
I can not imagine a situation where the number of Data Points equals the Floor number of a structure. (An arbitrary value chosen by a User.)


Generally, the structure of a UserForm is determined by the number of Data Points, (Variables) required.