For..Each..Next on Textboxes?

[Danny Ray]

Hello,
I was wondering if itis possible to use a For...Each Loop on Textboxes in a Userform.

Thanks!

 

[Scott Huish]

What did you want to do with the loop and how are the Textboxes named?

 

[Danny Ray]

Hello Hotpepper,
Thanks for responding. I want to perform an If Statement on
each Text box on the userform. Here is an example.

If textbox.Value="" Then Textbox.Value="0"

The Textboxes are named Textbox1, Textbox2, Etc.
Thanks for your help.

 

[Scott Huish]

Perhaps:

Private Sub CommandButton1_Click()
Dim ctl As Control
For Each ctl In UserForm1.Controls
    If Left(ctl.Name, 7) = "TextBox" Then
        If ctl = "" Then ctl = 0
    End If
Next
End Sub

 

[Danny Ray]

Thanks Hotpepper,
Your solution worked perfectly.

 

[Norie]

Danny

There is another couple of ways that will work however you have named the textboxes.

Private Sub CommandButton1_Click()
Dim ctl As Control
For Each ctl In UserForm1.Controls
    If TypeOf ctl Is MSForms.TextBox Then
        If ctl = "" Then ctl = 0
    End If
Next
End Sub

Private Sub CommandButton1_Click()
Dim ctl As Control
For Each ctl In UserForm1.Controls
    If TypeName(ctl) = "TextBox" Then
        If ctl = "" Then ctl = 0
    End If
Next
End Sub

 

[Danny Ray]

Thanks Norie,
That is what I was looking for. It will be very useful in the future, as I will be using a great many userforms. Hotpepper's suggestion worked well for my current situation, but I will most likely apply your method.

Once again, Thanks for the help.

 

[Danny Ray]

Another Textbox Question.

Hello again,
I am using the following to show a total in Textbox44 on a Userform. Is there a way to run
this when a change is made to any Textbox rather than placing it in the _Change Event
of each Textbox on the form?

TextBox44 =  Val(TextBox1) * Val(TextBox2) + Val(TextBox3) * Val(TextBox4) + _
Val(TextBox5) * Val(TextBox6) + Val(TextBox7) * Val(TextBox8) + _
Val (TextBox9) * Val(TextBox10) + Val(TextBox11) * Val(TextBox12) + _
Val(TextBox13) * Val(TextBox14) + Val(TextBox15) * Val(TextBox16) + _
Val(TextBox17) * Val(TextBox18) + Val(TextBox19) * Val(TextBox20) + _
Val(TextBox21) * Val(TextBox22) + Val(TextBox23) * Val(TextBox24) + _
Val(TextBox25) * Val(TextBox26) * Val(TextBox27) + _
Val(TextBox28) * Val(TextBox29) * Val(TextBox30) + _
Val(TextBox31) * Val(TextBox32) * Val(TextBox33) + _
Val(TextBox34) * Val(TextBox35) * Val(TextBox36)

 

[Norie]

Danny

The only way I can think of doing this it use a class module.

I'll have a look into it and try and post back.

It's a little late here, past 1am, and I'm probably not in the state of mind to see exactly what you are trying to do.

Perhaps if you explained exactly what you are trying to do it might help me or others.

Why do you have so many textboxes on a userform?

 

[Danny Ray]

Thanks Norie. Try to get some sleep. I will try to explain.

The textboxes in this particular userform are to be used in a calculation.
Without going into great detail, this is the method of calculation:

Textboxes 1, 3, 5, 7, 9, 11, 13, 15, 17, 19, 21, 23=Quantity
Textboxes 2, 4, 6, 8, 10, 12, 14, 16, 18, 20, 22=Height
Textboxes 25, 28, 31, 34=Quantity
Textboxes 26, 29, 32, 35=Width
Textboxes 27, 30, 33, 36=Length

The Textboxes are placed as follows:

1 @ 2......... 9 @ 10
3 @ 4....... 11 @ 12
5 @ 6....... 13 @ 14
7 @ 8........15 @ 16


17 @ 18.........25 @ 26 x 27
19 @ 20 ........28 @ 29 x 30
21 @ 22.........31 @ 32 x 33
23 @ 24.........34 @ 35 x 36

Total - 40

Total=(TB1 X TB2) + (TB3 X TB4) + (TB5 XTB6) + (TB7 X TB8) +
(TB9 X TB10) + (TB11 X TB12) + (TB13XTB14) + (TB15 X TB16) +
(TB17 X TB18) + (TB19 X TB20) + (TB21 XTB22) + (TB23 X TB24) +
(TB25 X TB26 X TB27) + (TB28 X TB29 X TB30) + (TB31 X TB32 X TB33) +
(TB34 X TB35 X TB36)

What I would like to have is as the user is inputting data, the total updates.
For now, I have placed the code(See above post) into the Textbox_Change
event of each Textbox 1-36. This works. However, I would like to know if
there is a way to place the code only once, I hope I have been clear.
Thanks to all who take the time to read and help.
I know I am a hopeless novice.