Sum of textboxes into label (loop?).

Posted by Beginner Bob on February 13, 2001 11:29 AM

I have the following which is the only thing I could come up with to work for what I'm trying to do:

Public Sub TOTIT()
Dim TB3 As Integer, TB4 As Integer, TB5 As Integer
On Error Resume Next
TB3 = TextBox3.Value
TB4 = TextBox4.Value
TB5 = TextBox5.Value
TTL = Format(TB3 + TB4 + TB5, "#,##0.00")
Label2.Caption = TTL
End Sub

Is there any easy way to loop through the textboxes to get the sum (I will be adding many more textboxes). I've seen "For each TB in Userform1.Controls" loops here, but don't know exactly how I could use that to sum only the 3 textboxes and put the value in the label. Any help is greatly appreciated.

Posted by Dave Hawley on February 13, 2001 11:37 PM

Hi B Bob

Here are 3 methods:

Dim TControl As Control, TbV As Integer
For Each TControl In Me.Controls
On Error Resume Next
Select Case TControl.Name
Case "TextBox1", "TextBox2", "TextBox3"
TbV = TControl.Value + TbV
End Select
Next TControl
MsgBox TbV

For Each TControl In Me.Controls
On Error Resume Next
If TControl.Name Like ("TextBox*") Then
TbV = TControl.Value + TbV
End If
Next TControl
MsgBox TbV

For Each TControl In Me.Controls
On Error Resume Next
If TControl.Tag = "AddMe" Then
TbV = TControl.Value + TbV
End If
Next TControl
MsgBox TbV

Hope they help

Dave

OzGrid Business Applications

Posted by Beginner Bob on February 14, 2001 4:29 PM

Re: Doesn't work for decimals???

Thanks for your help Dave, but there's one other thing with this...

I tried using the first method, and it works great for the loop. I changed the msgbox to Textbox6.Value = TbV, when I enter a number like 50.5 it gives me 51. I need to change this so that if "50" is entered in a textbox, Textbox6 says "50.00", or if "50.55" is entered, Textbox6 says "50.55"? I've tried Textbox6.Value = Format(TbV,"#,##0.00") along with most other formatting commands, but still can't get it right. Does this have to do with having "Dim TbV As Integer", because it doesn't work at all if I don't have that. Thanks again Dave.

Posted by Dave Hawley on February 14, 2001 5:42 PM

Re: Doesn't work for decimals???

Yes Bob, it will be due to TbV being declared as an Integer, try Dim Tbv as Double or If you have all sort then just Dim Tbv or Dim Tbv as Variant.

Dave

OzGrid Business Applications

Posted by Beginner Bob on February 15, 2001 9:39 AM

Thanks a lot Dave, got it now.

Here's what I have if anybody else is looking for something similar:

Private Sub TOTIT()
Dim TControl As Control, TbV As Double
For Each TControl In Me.Controls
On Error Resume Next
Select Case TControl.Name
Case "TextBox2", "TextBox3", "TextBox4", "TextBox5"
TbV = TControl.Value + TbV
End Select
Next TControl
Label9.Caption = Format(TbV, "#,##0.00")

End Sub

In each textbox change event I call TOTIT, and in each textbox exit event I format the textbox.