Add Controls Dynamically

[Ed]

I want to add a dozen (and perhaps 100) text boxes to a form dynamically.

The 'standard' code is:

Set oTextBox = Me.Controls.Add _
("Forms.TextBox.1", "txtLine" & CurrentCount)

However, as I add each control, I then need to change its attributes (width,
height, font, text alignment) etc. to the configuration I need. With 100 new
text boxes, that could take some time, even on a fast computer. (I know I
need to change "top" and "left", but I was hoping not to have to change
anything else.)

I presume that "Forms.TextBox.1" has the 'default attributes' already built
in.

Can I create a new "Forms.TextBox.1" (maybe a "Forms.TextBox.2") in VBA with
my own attributes?

-Ed (in Virginia)

 

[Gordon Bentley-Mix]

Ed,

Just for fun, I had a go at this. I was able to add 120 TextBoxes to a
UserForm "on-the-fly" with no appreciable delay in processing - and the
machine I'm using is far from flash: P4 with 1GB RAM.

This is the code I used to initialise the UserForm:

Sub UserForm_Initialize()
Dim i As Integer
Dim oTextBox As TextBox
If HowMany > 120 Then HowMany = 120
For i = 1 To HowMany
Set oTextBox = Me.Controls.Add("Forms.Textbox.1", "txtNew" & i, True)
With oTextBox
.Value = .Name
.Width = 100
With .Font
.Bold = True
.Italic = True
.Name = "Tahoma"
.Size = 9
End With
.TextAlign = fmTextAlignCenter
.BackColor = vbRed
.BorderColor = vbGreen
.BorderStyle = fmBorderStyleSingle
.ForeColor = vbBlue
.MaxLength = 20
If i <= 20 Then
.Top = i * 18
.Left = 6
ElseIf i > 20 And i <= 40 Then
.Top = (i - 20) * 18
.Left = 106
ElseIf i > 40 And i <= 60 Then
.Top = (i - 40) * 18
.Left = 206
ElseIf i > 60 And i <= 80 Then
.Top = (i - 60) * 18
.Left = 306
ElseIf i > 80 And i <= 100 Then
.Top = (i - 80) * 18
.Left = 406
ElseIf i > 100 And i <= 120 Then
.Top = (i - 100) * 18
.Left = 506
End If
End With
Next i
End Sub

It makes for a VERY ugly UserForm, but you can see I changed lots of
properties. Consequently, I don't think you need to worry about have a
"Forms.TextBox.2" control - not that it appears to be possible anyway.

Of course all this may be moot in light of Jonathan's answer in the third
instance of your "part 2" posts. (Did you get the hiccups or was your
connection just slow? <g>)
--
Cheers!
Gordon

Uninvited email contact will be marked as SPAM and ignored. Please post all
follow-ups to the newsgroup.

 

[Gordon Bentley-Mix]

Ed,

A bit of explanation on the previous - just in case you're curious.

"HowMany" is an Integer variable that records the value input into a TextBox
on a prefacing UserForm, but it could come from a variety of sources; e.g.
passed from another procedure; a TextBox on this UserForm; a ComboBox on this
or another UserForm; etc.

The UserForm that's being initialised by this code is approximately 450 by
620 - big enough to accommodate 120 TextBoxes with a .Width of 100. That's
the reason I limit "HowMany" to 120 or less.

The code for setting the .Top and .Left properties of the TextBoxes as
they're added is incredibly messy and could probably be made much more
efficient, but I couldn't be bothered for just a proof-of-concept.

I'm curious as to the practical application of this code. What exactly is
the business rule that you're trying to satisfy? There may be a better way.

For instance, if you are trying to collect multiple instances of similar
information, this could be accommodated in a variety of ways, such as adding
values to an array and then building functionality to work with the array
values. This might also solve the problem from your subsequent post as well.
--
Cheers!
Gordon

Uninvited email contact will be marked as SPAM and ignored. Please post all
follow-ups to the newsgroup.

 

[Ed]

Thanks! This was very helpful, but because I cannot set Enter, Exit, Click
etc. rules (an answer to another question on this board) I have to go a
different direction.

Ed