Hi ~

I've been going through the archives and I can't seem to find a
solution for my problem. I have created a form ("UserForm1") that
consists of 16 text boxes, 7 labels, and 4 check boxes. I've created a
process that will populate the 16 text boxes, but I have to
individually call out each of the 16 text boxes to populate data. ex.
UserForm1.txtTextBox1.Value = "blah" and so on. Is there an easier way
to do it? I only want to search for the text boxes and then populate
them w/ my data. I'm trying to make my code as generic as possible and
I'm not sure how to accomplish that. I'm a newbie at VBA so it's
taking a while to get a handle on it. So, here are my questions:
1. Is it possible to loop through each of the text boxes and populate
them with data? For example, "For Each <TextBox in
UserForm1.Controls....."
2. Also, is there a way to pass an array pointing to a particular text
box? Or, instead of typing the exact reference of the txtTextBox, can
I pass the reference as a paramater? For example, I would like to have
the value of "blah5" in UserForm1.txtTextBox5.Value.
This is what I have so far....
Example:
Dim myTextBox as TextBox
Dim myFormCtrl as Control

For each myFormCtrl in UserForm1.Controls
If TypeName(myFormCtrl) = "TextBox" Then
<What do I do here?
End If
Next

I hope that I've explained the problem for you. Any help that you
could provide would be very much appreciated.

Check this out, or search for control array.

http://builder.com.com/5100-6228-5388637.html

-John

"Phantom" wrote:

Hi ~

I've been going through the archives and I can't seem to find a
solution for my problem. I have created a form ("UserForm1") that
consists of 16 text boxes, 7 labels, and 4 check boxes. I've created a
process that will populate the 16 text boxes, but I have to
individually call out each of the 16 text boxes to populate data. ex.
UserForm1.txtTextBox1.Value = "blah" and so on. Is there an easier way
to do it? I only want to search for the text boxes and then populate
them w/ my data. I'm trying to make my code as generic as possible and
I'm not sure how to accomplish that. I'm a newbie at VBA so it's
taking a while to get a handle on it. So, here are my questions:
1. Is it possible to loop through each of the text boxes and populate
them with data? For example, "For Each <TextBox in
UserForm1.Controls....."
2. Also, is there a way to pass an array pointing to a particular text
box? Or, instead of typing the exact reference of the txtTextBox, can
I pass the reference as a paramater? For example, I would like to have
the value of "blah5" in UserForm1.txtTextBox5.Value.
This is what I have so far....
Example:
Dim myTextBox as TextBox
Dim myFormCtrl as Control

For each myFormCtrl in UserForm1.Controls
If TypeName(myFormCtrl) = "TextBox" Then
<What do I do here?
End If
Next

I hope that I've explained the problem for you. Any help that you
could provide would be very much appreciated.

VBA doesn't have control arrays, although you can set up a class module to
handle events from a group of controls:

Handle Multiple UserForm Buttons With One Procedure
http://www.j-walk.com/ss/excel/tips/tip44.htm

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"John Bundy" wrote in message
...
Check this out, or search for control array.

http://builder.com.com/5100-6228-5388637.html

-John

"Phantom" wrote:

Hi ~

I've been going through the archives and I can't seem to find a
solution for my problem. I have created a form ("UserForm1") that
consists of 16 text boxes, 7 labels, and 4 check boxes. I've created a
process that will populate the 16 text boxes, but I have to
individually call out each of the 16 text boxes to populate data. ex.
UserForm1.txtTextBox1.Value = "blah" and so on. Is there an easier way
to do it? I only want to search for the text boxes and then populate
them w/ my data. I'm trying to make my code as generic as possible and
I'm not sure how to accomplish that. I'm a newbie at VBA so it's
taking a while to get a handle on it. So, here are my questions:
1. Is it possible to loop through each of the text boxes and populate
them with data? For example, "For Each <TextBox in
UserForm1.Controls....."
2. Also, is there a way to pass an array pointing to a particular text
box? Or, instead of typing the exact reference of the txtTextBox, can
I pass the reference as a paramater? For example, I would like to have
the value of "blah5" in UserForm1.txtTextBox5.Value.
This is what I have so far....
Example:
Dim myTextBox as TextBox
Dim myFormCtrl as Control

For each myFormCtrl in UserForm1.Controls
If TypeName(myFormCtrl) = "TextBox" Then
<What do I do here?
End If
Next

I hope that I've explained the problem for you. Any help that you
could provide would be very much appreciated.

Sorry, he's right of course, Ive been working in VB6.

"Jon Peltier" wrote:

VBA doesn't have control arrays, although you can set up a class module to
handle events from a group of controls:

Handle Multiple UserForm Buttons With One Procedure
http://www.j-walk.com/ss/excel/tips/tip44.htm

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"John Bundy" wrote in message
...
Check this out, or search for control array.

http://builder.com.com/5100-6228-5388637.html

-John

"Phantom" wrote:

Hi ~

I've been going through the archives and I can't seem to find a
solution for my problem. I have created a form ("UserForm1") that
consists of 16 text boxes, 7 labels, and 4 check boxes. I've created a
process that will populate the 16 text boxes, but I have to
individually call out each of the 16 text boxes to populate data. ex.
UserForm1.txtTextBox1.Value = "blah" and so on. Is there an easier way
to do it? I only want to search for the text boxes and then populate
them w/ my data. I'm trying to make my code as generic as possible and
I'm not sure how to accomplish that. I'm a newbie at VBA so it's
taking a while to get a handle on it. So, here are my questions:
1. Is it possible to loop through each of the text boxes and populate
them with data? For example, "For Each <TextBox in
UserForm1.Controls....."
2. Also, is there a way to pass an array pointing to a particular text
box? Or, instead of typing the exact reference of the txtTextBox, can
I pass the reference as a paramater? For example, I would like to have
the value of "blah5" in UserForm1.txtTextBox5.Value.
This is what I have so far....
Example:
Dim myTextBox as TextBox
Dim myFormCtrl as Control

For each myFormCtrl in UserForm1.Controls
If TypeName(myFormCtrl) = "TextBox" Then
<What do I do here?
End If
Next

I hope that I've explained the problem for you. Any help that you
could provide would be very much appreciated.

Hi guys ~

thanks so much for all your help. I ended up going with Jon Peltier's
suggestion. Thanks to everyone to responded. I really appreciate the
help. My code works fine now.

On Dec 4, 4:06 pm, John Bundy
wrote:
Sorry, he's right of course, Ive been working in VB6.

"Jon Peltier" wrote:
VBA doesn't have control arrays, although you can set up a class module to
handle events from a group of controls:

Handle Multiple UserForm Buttons With One Procedure
http://www.j-walk.com/ss/excel/tips/tip44.htm

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

"John Bundy" wrote in message
...
Check this out, or search for control array.

http://builder.com.com/5100-6228-5388637.html

-John

"Phantom" wrote:

Hi ~

I've been going through the archives and I can't seem to find a
solution for my problem. I have created a form ("UserForm1") that
consists of 16textboxes, 7 labels, and 4 checkboxes. I've created a
process that will populate the 16textboxes, but I have to
individually call out each of the 16textboxesto populate data. ex.
UserForm1.txtTextBox1.Value = "blah" and so on. Is there an easier way
to do it? I only want to search for thetextboxesand then populate
them w/ my data. I'm trying to make my code as generic as possible and
I'm not sure how to accomplish that. I'm a newbie at VBA so it's
taking a while to get a handle on it. So, here are my questions:
1. Is it possible to loop through each of thetextboxesand populate
them with data? For example, "For Each <TextBox in
UserForm1.Controls....."
2. Also, is there a way to pass an array pointing to a particulartext
box? Or, instead of typing the exact reference of the txtTextBox, can
I pass the reference as a paramater? For example, I would like to have
the value of "blah5" in UserForm1.txtTextBox5.Value.
This is what I have so far....
Example:
Dim myTextBox as TextBox
Dim myFormCtrl as Control

For each myFormCtrl in UserForm1.Controls
If TypeName(myFormCtrl) = "TextBox" Then
<What do I do here?
End If
Next

I hope that I've explained the problem for you. Any help that you
could provide would be very much appreciated.

You could use code like

Dim Ctrl As MSForms.Control
For Each Ctrl In Userform1.Controls
If TypeOf Ctrl Is MSForms.TextBox Then
' do something with Ctrl
End If
Next Ctrl


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


"Phantom" wrote in message
ps.com...
Hi ~

I've been going through the archives and I can't seem to find a
solution for my problem. I have created a form ("UserForm1") that
consists of 16 text boxes, 7 labels, and 4 check boxes. I've created a
process that will populate the 16 text boxes, but I have to
individually call out each of the 16 text boxes to populate data. ex.
UserForm1.txtTextBox1.Value = "blah" and so on. Is there an easier way
to do it? I only want to search for the text boxes and then populate
them w/ my data. I'm trying to make my code as generic as possible and
I'm not sure how to accomplish that. I'm a newbie at VBA so it's
taking a while to get a handle on it. So, here are my questions:
1. Is it possible to loop through each of the text boxes and populate
them with data? For example, "For Each <TextBox in
UserForm1.Controls....."
2. Also, is there a way to pass an array pointing to a particular text
box? Or, instead of typing the exact reference of the txtTextBox, can
I pass the reference as a paramater? For example, I would like to have
the value of "blah5" in UserForm1.txtTextBox5.Value.
This is what I have so far....
Example:
Dim myTextBox as TextBox
Dim myFormCtrl as Control

For each myFormCtrl in UserForm1.Controls
If TypeName(myFormCtrl) = "TextBox" Then
<What do I do here?
End If
Next

I hope that I've explained the problem for you. Any help that you
could provide would be very much appreciated.