Simple problem, no solution yet: I have a UserForm with 50 TexBoxes
named TextBox1, TextBox2 ...TextBox49. I have a row of 50 adjacent
cells who's values need to be loaded into the TextBoxes. Cell one in
the row corresponds to TextBox1 in the UserForm, cell two with TextBox2
and so on.

As it stands, I have to use fifty lines of code to load the values and
then 50 more to store them when the user exits the UserForm. I can't
find a way to index the TextBoxes and load them using a For or While
loop. Presumably then, I could save the changed TextBoxes back to the
sheet using a similar loop. Any ideas?

Thanks in advance,
--Kim

Hambone wrote:
Simple problem, no solution yet: I have a UserForm with 50 TexBoxes
named TextBox1, TextBox2 ...TextBox49. I have a row of 50 adjacent
cells who's values need to be loaded into the TextBoxes. Cell one in
the row corresponds to TextBox1 in the UserForm, cell two with TextBox2
and so on.

As it stands, I have to use fifty lines of code to load the values and
then 50 more to store them when the user exits the UserForm. I can't
find a way to index the TextBoxes and load them using a For or While
loop. Presumably then, I could save the changed TextBoxes back to the
sheet using a similar loop. Any ideas?

Thanks in advance,
--Kim


Dim TxtBoxes as varinat

TxtBoxes = Array (TextBox1, TextBox2, TextBox3)


for i = lbound (txtBoxex) to ubound (txtboxes)

txtBoxes (i).Add (i)

next i

Hambone wrote:
Simple problem, no solution yet: I have a UserForm with 50 TexBoxes
named TextBox1, TextBox2 ...TextBox49. I have a row of 50 adjacent
cells who's values need to be loaded into the TextBoxes. Cell one in
the row corresponds to TextBox1 in the UserForm, cell two with TextBox2
and so on.

As it stands, I have to use fifty lines of code to load the values and
then 50 more to store them when the user exits the UserForm. I can't
find a way to index the TextBoxes and load them using a For or While
loop. Presumably then, I could save the changed TextBoxes back to the
sheet using a similar loop. Any ideas?

Thanks in advance,
--Kim

Kim,

Another alternative would be to create a control array at design time
(rather than at run time as the previous explanation). To do this, you
name all of the textboxes the same and give them incrementing index
attribute. I would guess that VBA will ask if you want to create a
control array as soon as you try to name two of them the same (VB6
does)... Look uo control arrays in the help files for more information
on how to use them. You can then execute simple loops to load/store
the data based on the events you want to trigger them (on form load, on
button press, etc...)

David

Another alternative would be to create a control array at design time
(rather than at run time as the previous explanation). To do this, you
name all of the textboxes the same and give them incrementing index
attribute. I would guess that VBA will ask if you want to create a
control array as soon as you try to name two of them the same (VB6
does)... Look uo control arrays in the help files for more information
on how to use them. You can then execute simple loops to load/store
the data based on the events you want to trigger them (on form load, on
button press, etc...)

David

A Control Array in the VB6 sense is not available in VBA userforms as was
pointed out by many just the other day.

http://tinyurl.com/n8vvs

Look into an array or collection of class's of With Events msforms.TextBox,
many examples in this ng.

Regards,
Peter T