| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each Next loop Through Userform
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
For Each Next loop Through Userform
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 wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
For Each Next loop Through Userform
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" wrote in message ... 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 wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
For Each Next loop Through Userform
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 wrote: 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" wrote in message ... 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 wrote: 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 -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
For Each Next loop Through Userform
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" wrote in message ... 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 wrote: 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" wrote in message ... 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 wrote: 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 -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
For Each Next loop Through Userform
Not for me.
John Shikella wrote: 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" wrote in message ... 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 wrote: 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" wrote in message ... 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 wrote: 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 -- Dave Peterson -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
For Each Next loop Through Userform
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 "John Shikella" wrote in message ... 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" wrote in message ... 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 wrote: 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" wrote in message ... 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 wrote: 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 -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
For Each Next loop Through Userform
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. "Tom Ogilvy" wrote in message ... 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 "John Shikella" wrote in message ... 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" wrote in message ... 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 wrote: 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" wrote in message ... 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 wrote: 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 -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
For Each Next loop Through Userform
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. -- Regards, Tom Ogilvy "John Shikella" wrote in message ... 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. "Tom Ogilvy" wrote in message ... 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 "John Shikella" wrote in message ... 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" wrote in message ... 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 wrote: 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" wrote in message ... 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 wrote: 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 -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
For Each Next loop Through Userform
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 "Tom Ogilvy" wrote in message ... 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. -- Regards, Tom Ogilvy "John Shikella" wrote in message ... 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. "Tom Ogilvy" wrote in message ... 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 "John Shikella" wrote in message ... 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" wrote in message ... 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 wrote: 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" wrote in message ... 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 wrote: 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 -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Loop based on number entered in userform | Excel Worksheet Functions | |||
| Loop through userform | Excel Discussion (Misc queries) | |||
| Need a macro to loop evaluation of many textboxes in a UserForm. | Excel Programming | |||
| Looping procedure calls userform; how to exit loop (via userform button)? | Excel Programming | |||
| Using For/Next Loop To Generate 4 CommandButtons On A UserForm Results In A Runtime Error 91 | Excel Programming | |||
Linear Mode
