For Each Next loop Through Userform

[John Shikella]

Before I get into it, I think the issue may be the lowercase 'textbox' and
why that is happening. Any ideas or I will get more specific. I am using
many .controlsource and mulitpage form. Thanks.

Sub TextBox24_Change()
Dim Ctrl as Control
For Each Ctrl In UserForm20.Controls
If TypeOf Ctrl Is MSForms.textbox Then
For i = 25 To 30
If Ctrl.Name = "TextBox" & i Then
Ctrl.Value = TextBox24.Value
End If
Next i
End If
Next


End Sub

John

 

[Dave Peterson]

If you know the names of the textboxes (textbox25 to textbox30), you can just
use them:

Sub TextBox24_Change()
dim i as long
for i = 25 to 30
me.controls("textbox" & i).value = me.textbox24.value
next i

End Sub

 

[John Shikella]

Agreed and thank you Dave for taking a look.

However, that does not solve my issue. I believe the "MSForms.textbox" is
signaling an underlying issue to me. I will explain. I am making a
calculator to calculate complex discounts from list rates. Lets say I have
1 row of 5 columns of textboxes. I want the user to be able to enter the
discount into the first textbox and have the remaining 5 default that #.
Easy enough right? I have done it hundreds of times. Well what is
happening is when I enter the # into the textbox24 field, it does not
populate there, it populates in the 25th of the i=25 to 30 then if I enter
it again it goes to the 26th , then 27th, etc. When I debug with
"Debug.Print Ctrl.Value, Ctrl.Name, TextBox24.Value, Ctrl.ControlSource"
this is what I get. This is what happens when I enter "23" into the default
box.

Thanks so much once again. The 4,000 sales reps here at FedEx that will
benefit from this also thank you.

John


2 TextBox25 2 DATA!f33
2 TextBox26 2 DATA!e33
2 TextBox27 2 DATA!j33
2 TextBox28 2 DATA!i33
2 TextBox29 2 DATA!h33
2 TextBox30 2 DATA!g33
TextBox25 DATA!f33
TextBox26 DATA!e33
TextBox27 DATA!j33
TextBox28 DATA!i33
TextBox29 DATA!h33
TextBox30 DATA!g33
3 TextBox25 3 DATA!f33
3 TextBox26 3 DATA!e33
3 TextBox27 3 DATA!j33
3 TextBox28 3 DATA!i33
3 TextBox29 3 DATA!h33
3 TextBox30 3 DATA!g33
TextBox25 DATA!f33
TextBox26 DATA!e33
TextBox27 DATA!j33
TextBox28 DATA!i33
TextBox29 DATA!h33
TextBox30 DATA!g33

 

[Dave Peterson]

I'm confused.

You have 1 row of 5 textboxes. If you fill in the first, you want to populate
the remaining 5??? There should be only 4 remaining.

It sounds like you have 30 textboxes. Are they named textbox1 through textbox30
and laid out like:

textbox1 textbox2 textbox3 textbox4 textbox5
textbox6 textbox7 textbox8 textbox9 textbox10
textbox11 textbox12 textbox13 textbox14 textbox15
textbox16 textbox17 textbox18 textbox19 textbox20
textbox21 textbox22 textbox23 textbox24 textbox25
textbox26 textbox27 textbox28 textbox29 textbox30


1-5 going across. 6-10, ... 26-30?

And if you fill in any textbox, should those textboxes directly to the right be
populated with the same value?

If that's what you mean, then maybe something like this:

Option Explicit
Dim BlkProc As Boolean
Private Sub TextBox1_Change()
If BlkProc = True Then Exit Sub
Call DoTheWork(1)
End Sub
Private Sub TextBox2_Change()
If BlkProc = True Then Exit Sub
Call DoTheWork(2)
End Sub
Private Sub TextBox3_Change()
If BlkProc = True Then Exit Sub
Call DoTheWork(3)
End Sub
Private Sub TextBox4_Change()
If BlkProc = True Then Exit Sub
Call DoTheWork(4)
End Sub
Private Sub TextBox5_Change()
If BlkProc = True Then Exit Sub
Call DoTheWork(5)
End Sub
Private Sub TextBox6_Change()
If BlkProc = True Then Exit Sub
Call DoTheWork(6)
End Sub


'etc, do all 30 textboxes!


Private Sub TextBox26_Change()
If BlkProc = True Then Exit Sub
Call DoTheWork(26)
End Sub
Private Sub TextBox27_Change()
If BlkProc = True Then Exit Sub
Call DoTheWork(27)
End Sub
Private Sub TextBox28_Change()
If BlkProc = True Then Exit Sub
Call DoTheWork(28)
End Sub
Private Sub TextBox29_Change()
If BlkProc = True Then Exit Sub
Call DoTheWork(29)
End Sub
Private Sub TextBox30_Change()
If BlkProc = True Then Exit Sub
Call DoTheWork(30)
End Sub
Sub DoTheWork(WhichTB As Long)
Dim WhichRow As Long
Dim iCtr As Long
WhichRow = (WhichTB - 1) \ 5 + 1

If WhichTB = WhichRow * 5 Then
'do nothing, last in row
Else
BlkProc = True
For iCtr = WhichTB + 1 To (WhichRow * 5)
Me.Controls("textbox" & iCtr).Value _
= Me.Controls("textbox" & WhichTB).Value
Next iCtr
BlkProc = False
End If

End Sub

If this isn't close, you may want to describe the layout of the all the
textboxes and what you want.

 

[John Shikella]

Imagine this:

Pound Zone 2 Zone 3 Zone 4 Zone 5
Zone 6 Zone 7 Zone 8
1 - 10lbs textbox24 textbox25 textbox26 textbox27
textbox28 textbox29 textbox30

When textbox24 is changed, the others will as well. I have 11-30, 31-50,
etc but htis is just an example.

Does this clear things up at all.

 

[Dave Peterson]

Not for me.

Imagine this:

Pound Zone 2 Zone 3 Zone 4 Zone 5
Zone 6 Zone 7 Zone 8
1 - 10lbs textbox24 textbox25 textbox26 textbox27
textbox28 textbox29 textbox30

When textbox24 is changed, the others will as well. I have 11-30, 31-50,
etc but htis is just an example.

Does this clear things up at all.

 

[Tom Ogilvy]

If the point is to get it to work, why not get explicit

Sub TextBox24_Change()
Dim s as String
With me
s = .Textbox24.Value
.textbox25.Value = s
.textbox26.Value = s
.textbox27.Value = s
.textbox28.Value = s
.textbox29.Value = s
.textbox30.Value = s
End With
End With

--
Regards,
Tom Ogilvy

Imagine this:

Pound Zone 2 Zone 3 Zone 4 Zone 5
Zone 6 Zone 7 Zone 8
1 - 10lbs textbox24 textbox25 textbox26 textbox27
textbox28 textbox29 textbox30

When textbox24 is changed, the others will as well. I have 11-30, 31-50,
etc but htis is just an example.

Does this clear things up at all.

 

[John Shikella]

Thanks Tom.

That does not work either however. I know what I am saying sounds silly and
non relevant, but I really believe that the lower case textbox in "If TypeOf
Ctrl Is MSForms.textbox Then" is telling me something. Why would that not
pop into caps (MSForms.TextBox) like it does in other projects? Shouldn't
that reference it in the controls. I really feel its not in the methods but
something underlying. Maybe a reference issue or corrupt reference file or
something. When I debug it it looks perfect.

By the way, your code snippets and Ron De Bruins google search add in have
saved me countless times. If I see your name I know I can get back on
track. So thanks not only for this but all you've unknowingly done in the
past.

If the point is to get it to work, why not get explicit

Sub TextBox24_Change()
Dim s as String
With me
s = .Textbox24.Value
.textbox25.Value = s
.textbox26.Value = s
.textbox27.Value = s
.textbox28.Value = s
.textbox29.Value = s
.textbox30.Value = s
End With
End With

 

[Tom Ogilvy]

Try retyping it.

when you get to the MSforms. you should be confronted with a pick list that
includes TextBox.

But I would agree that if it doesn't capitalize, then there probably is a
problem. You haven't name a module or a sub as Textbox?

You haven't defined a variable as Textbox?

I assume you do have a valid reference to the MSforms library in
Tools=>References.

 

[John Shikella]

Thanks Tom. I believe I have the correct reference. I am going to try it
on another computer and see if that works. I will also do a search for the
word non-case-sensitive word "textbox" throughout the entire procedure and
see what that brings. I'll let you know.

john