Dynamically created textboxes in VBA userforms

[Harry-Wishes]

I am trying to programmatically place a certain number of textboxes on a
blank userform. Essentially the number of textboxes dynamically created will
be dependent on user input made through an inputbox from a previous screen.
Is there a way of doing this and also specifying where each textbox will be
placed on the blank form when the form appears on the uiser's screen? Of
course, I want to avoid textbox creation at design time since that locks me
into specifying a predefined number of textboxes (not always the number of
textboxes I will want at runtime). Is there a textbox object bulit into VBA.
Word's help was not very useful.

Thanks

Harry-Wishes

 

[Jay Freedman]

I am trying to programmatically place a certain number of textboxes
on a blank userform. Essentially the number of textboxes dynamically
created will be dependent on user input made through an inputbox from
a previous screen. Is there a way of doing this and also specifying
where each textbox will be placed on the blank form when the form
appears on the uiser's screen? Of course, I want to avoid textbox
creation at design time since that locks me into specifying a
predefined number of textboxes (not always the number of textboxes I
will want at runtime). Is there a textbox object bulit into VBA.
Word's help was not very useful.

Thanks

Harry-Wishes

To demonstrate, create a userform containing one (design-time) textbox named
txtNumBoxes and one command button named cmdAddBoxes. In the Tools >
References dialog, put a check mark on "Microsoft Forms 2.0 Object Library".
Paste in this code and watch how it executes:

Private Sub cmdAddBoxes_Click()
Dim idx As Long
Dim maxBoxes As Long
Dim x As Long, y As Long
Dim ctl As Control
Dim newBox As MSForms.TextBox

maxBoxes = Val(txtNumBoxes.Text)
If (maxBoxes > 0) And (maxBoxes <= 10) Then
' remove any existing boxes
For Each ctl In Me.Controls
If Len(ctl.Tag) > 2 Then
If Left(ctl.Tag, 3) = "new" Then
Controls.Remove (ctl.Name)
End If
End If
Next

For idx = 1 To maxBoxes
Set newBox = Me.Controls.Add("Forms.TextBox.1")
With newBox
.Tag= "new" & .Name
' make two columns of boxes
If idx < 6 Then
.Left = 10
.Top = 16 + 24 * idx
Else
.Left = 100
.Top = 16 + 24 * (idx - 5)
End If
End With
Next
Else
MsgBox "Number must be 1 to 10"
End If
End Sub

Of course, you can use whatever mechanism is already in place to get the
number of boxes, and you can use whatever scheme makes sense for assigning
the .Left and .Top values so the boxes appear in the right places. You can
assign the .Name property of each box if you want, and you can assign other
properties such as the .TabIndex. If the number of boxes requested is large
than will fit on the userform, you can also change the .Height and/or .Width
properties of the userform itself.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

 

[Gordon Bentley-Mix on news.microsoft.com]

Harry,

See the VBA help topics on the Controls Collection. It would appear from the
related topic on the Add method that it should be possible to add Controls to
a UserForm at runtime. However, in my brief experimentation, I didn't have
much joy. In addition, I suspect it might be overly complicated trying to set
the position of each TextBox dynamically. Would it not be easier to add a
reasonable number of TextBoxes to the UserForm at design time and then
hide/show the required number of TextBoxes at runtime? I do something similar
using a variation of the following:

Private Sub ShowFields(NumberToShow As Integer)
Dim i As Integer
For i = 1 To 10
Me.Controls("lblMyLabel" & i).Visible = False
Me.Controls("txtMyTextBox" & i).Visible = False
Next i
If NumberToShow > 0 Then
For i = 1 To NumberToShow
Me.Controls("lblMyLabel" & i).Visible = True
With Me.Controls("txtMyTextBox" & i)
.Visible = True
.Value = ""
End With
Next i
End If
End Sub

I call this sub in the change event of a ComboBox as follows (sort of):

Private Sub cboMyComboBox_Change()
ShowFields (cboMyComboBox.ListIndex)
End Sub

The above assumes 10 TextBoxes, named "txtMyTextBox[1-10]", related Labels,
named "lblMyLabel[1-10]", and a ComboBox, name "cboMyComboBox" with 11
ListItems from "0" to "10". In my application, the ComboBox and the TextBoxes
and Labels are all on the same UserForm, but it shouldn't be too difficult to
adapt this to your particular application.

(NOTE: A special thanks to Greg Maxey for helping me develop this code and
to make it as efficient as possible. See http://gregmaxey.mvps.org/ for more
good stuff like this.)
--
Cheers!

Gordon Bentley-Mix
Word MVP

Please post all follow-ups to the newsgroup.

Read the original version of this post in the Office Discussion Groups - no
membership required!