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.
>
>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
news:4DEFFB0C-F7A5-49D4-8242-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:4DEFFB0C-F7A5-49D4-8242-(E-Mail Removed)...
> > 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 <JohnBu...@discussions.microsoft.com>
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" <JohnBu...@discussions.microsoft.com> wrote in message
> >news:4DEFFB0C-F7A5-49D4-8242-(E-Mail Removed)...
> > > 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.