For...Next question for userform update

Posted by JAF on July 31, 2001 7:55 AM

Simplified version of my problem:

I have a user form which has 3 text boxes for numerical input. The boxes are named txt_input1, txt_input2 & txt_input3.

I also have 3 labels which are named lbl_result1, lbl_result2 & lbl_result3.

On clicking the "Calculate" command button, I want to perform the same calculation on the contents of each of the text boxes (let's say that I want to add 1.25 to each text box value) and display the result in the appropriate label next to each text box.

I know that I could use:
lbl_result1.caption = txt_input1.value +1.25
lbl_result2.caption = txt_input2.value +1.25
lbl_result3.caption = txt_input2.value +1.25

...but this is not the most "elegant" solution, and in practice, I have a lot more than 3 input boxes and the calculation for each is a lot more complicated.

I'm guessing that I need to use a For...Next loop to refer to each of the text boxes and labels in turn, something along the lines of:
For i = 1 to 3 Step 1
'code here
Next i

...but i can't figure out the 'code here part.

Any suggestions???

Posted by Russell on July 31, 2001 9:25 AM

I would put code in the AfterUpdate event of each textbox, something like this:

Private Sub txt_input1_AfterUpdate()
Me.lbl_result1.Caption = Me.txt_input1.Value + 1.25
End Sub

You can basically cut and paste, just changing the numbers of each (1 to 2, etc. -- you can do a find and replace on each selection).

However, if you would really like to do it via a button, you could try something like this:

Private Sub cmdCalculate_Click()
Dim ctl As Control

For Each ctl In Me.Controls
If Left(ctl.Name, 3) = "txt" Then
Me.Controls("lbl_result" & Right(ctl.Name, 1)).Caption = ctl.Value
End If
Next ctl

End Sub

Hope this helps,

Russell