userform testbox to create number of textbox to another userform

Hi Experts!

I am creating a form where there will be multiple userforms. So I have "userform1", textbox1 is where I'll put a number, say "10" for example, is it possible to get to "userform2" and have 10 textboxes? and if "5" is entered from "userform1", 5 textboxes will be created in "userform2"?

Hi Markus,

The following code will add textbox(s) to a second userform from the number entered in a textbox on the first userform. The code assumes the following but you can change them:

FIRST USERFORM:
Name: arbitrary
one textbox name: TextBox1
one commandbutton name: CommandButton1

SECOND USERFORM:
Name: UserForm2
Can include any objects you like except textboxes named "TextBox1, TextBox2, etc."

Private Sub CommandButton1_Click()
'===================================
'CODE TO ADD TEXTBOX(s) WITH PARAMETERS
'FROM USERFORM1 TO USERFORM2
'===================================
Dim tbox As Control
Dim StartTop As Integer, I As Integer
StartTop = 50
'-----------------------------------
'CREATE TEXTBOXES # SET BY TEXTBOX1
For I = 1 To Val(TextBox1.Value)
'-----------------------------------
'ADD TEXTBOX AND SET TEXTBOX PARAMETERS
    Set tbox = UserForm2.Controls.Add("Forms.TextBox.1") 'CREATE INSTANCE OF TEXTBOX
    tbox.Name = "TextBox" & I
    tbox.Left = 30
    tbox.Top = StartTop:: StartTop = StartTop + 20
    tbox.Height = 16.2
    tbox.Width = 90
    tbox.Object.Value = ""
    Set tbox = Nothing
Next I
UserForm2.Show
End Sub

You can change the parameters (height, width, left, top, value, naming convention, etc). Remember that the textboxes are created at runtime and will not be prest after the code is finished running. Therefore, you want to initiate Userform2 in the code from commandButton1 on UserForm1 and continue running your code from the second userform in a continuous flow.

If possible, a better approach is to have all the textboxes with .visible = FALSE on userform2 at design time and set their .visibility to TRUE as needed.

HTH,
Maud

what do you mean?

I had verbiage that was being picked up as an HTML tag. I removed it and it enabled me to post....see above.

Maud

thank you so much Maudibe! Your code worked like a charm. One more thing, is there a way that userform2 can have like 40 textboxes and have 4 columns to fit them (10 textbox per column).

Markus,

Here is the code revised so that multiple column of textboxes can be created. Like the previous code Userform1 must have a textbox called Textbox1 and a button called CommandButton1. Userform2 can be blank or have any objects you please as long as they are not named Textbox1, TextBox2, etc..

**NEW**
when you launch Userform1, in Textbox1 enter the number of total textboxes, a "/", and the number of columns (eg. 34/4) which yield 34 total textboxes in 4 columns. The code auto-arranges the remainder of the textboxes. The height and width of the form are automatically calculated using the number of columns, width, and height of the textboxes.

HTH,
Maud

Add Textboxes.png

Private Sub CommandButton1_Click()
'===================================
'CODE TO ADD TEXTBOX(s) WITH PARAMETERS
'FROM USERFORM1 TO USERFORM2
'===================================
Dim tbox As Control, numcol()
Dim boxLeft As Integer, boxTop As Integer
Dim boxHeight As Long, boxWidth As Long
Dim I As Integer, J As Integer, index As Integer
Dim num As Integer, cols As Integer, remainder As Integer
num = Val(Split(TextBox1.Value, "/")(0)) 'NUMBER OF TEXTBOXES
cols = Val(Split(TextBox1.Value, "/")(1)) 'NUMBER OF COLUMNS
remainder = num Mod cols 'REMAINDER OF TEXTBOXES/COLUMNS
num = Int(num / cols) 'NUMBER OF TEXTBOXES/COLUMNS- ROUND TO INTEGER
'-----------------------------------
'EDITABLE PARAMETERS- THESE CAN BE CHANGED TO SUIT NEEDS
boxLeft = 50     'LEFT COLUMN MARGIN INTERVAL
boxTop = 50      'START OF FIRST ROW OF TEXTBOXES
interval = 20    'ROWS INTERVALS FROM START
boxHeight = 16.2 'HEIGHT OF TEXT BOXES (MUST BE LESS THAN INTERVAL)
boxWidth = 40    'WIDTH OF TEXTBOXES (MUST BE LESS THEN BOXLEFT INTEVAL)
'-----------------------------------
'NON-EDITABLE PARAMETERS- DO NOT CHANGE
index = 1 'COUNTER FOR TEXTBOX NAME
ReDim numcol(cols + 1) 'SIZES ARRAY OF NUMBER OF COLUMNS +1
'-----------------------------------
'CREATE COLUMNS OF TEXTBOXWS
For I = 1 To cols
'-----------------------------------
'DETERMINE NUMBER OF TEXTBOXES IN EACH COLUMN INCLUDING REMAINDER
    If remainder > 0 Then
        numcol(I) = num + 1:: remainder = remainder - 1
    Else
        numcol(I) = num
    End If
'-----------------------------------
'ADD TEXTBOX AND SET TEXTBOX PARAMETERS
    For J = 1 To numcol(I)
        Set tbox = UserForm2.Controls.Add("Forms.TextBox.1") 'CREATE INSTANCE OF TEXTBOX
        tbox.Name = "TextBox" & index:: index = index + 1
        tbox.Left = boxLeft * I
        tbox.Top = boxTop:: boxTop = boxTop + interval
        tbox.Height = boxHeight
        tbox.Width = boxWidth
        tbox.Object.Value = ""
        Set tbox = Nothing
    Next J
    boxTop = 50 'SET TO THE SAME AS BOXTOP
Next I
'-----------------------------------
'ADJUST WIDTH AND HEIGHT OF USERFORM2 THEN DISPLAY
UserForm2.Width = boxLeft * (cols + 2)
UserForm2.Height = (interval * numcol(1)) + (boxTop * 2)
UserForm2.Show
Unload Me
End Sub

I totally appreciate your time for helping me on this, it works perfectly! now I just have to figure out if it's possible to have all the entries from all my userforms be transferred to just one worksheet.

This will do it:

Public Sub test()
For I = 1 To num 'NUM = # OF TEXTBOXES ON USERFORM2
    ActiveSheet.Cells(I, 1) = Userform2.Controls("Textbox" & num).value
Next I
End Sub