Adding Time Within Textboxes

[Try2Live4God]

Hi,
I was trying to search but couldn't find anything related to what I'd like to do. But then again, I'm still somewhat of a novice with VBA.....

Anyways....I have 4 textboxes that have the total overtime hrs. I'd like to add up the values/times from these 4 textboxes and get a Grand Total of the OT hrs which will display in a fifth textbox. I want to stick to using textboxes and not use the cells.

Example:
Textbox1 = 1:00 of OT
Textbox2 = 1:30 of OT
Textbox3 = 2:00 of OT
Textbox4 = 2:45 of OT
Textbox5 = Grand Total of 7:15

Any help would be appreciated.

Thanks!

 

[Trevor G]

Are the textboxes on a vba userform?

 

[Try2Live4God]

No it is not on a userform. I just have Textboxes on the worksheet.

 

[Trevor G]

Try this, go into design mode on each textbox and add the following code by double clicking the textbox one at time, it will use the change event.

I have created a subroutine sumtxtbox within the module sheet.

Sub SumTxtBox()
On Error Resume Next
Me.TextBox4 = (IIf(Me.TextBox1 = "", 0, Me.TextBox1) + 0) + (IIf(Me.TextBox2 = "", 0, Me.TextBox2) + 0) + (IIf(Me.TextBox3 = "", 0, Me.TextBox3) + 0)
End Sub
Private Sub TextBox1_Change()
Call SumTxtBox
End Sub
Private Sub TextBox2_Change()
Call SumTxtBox
End Sub
Private Sub TextBox3_Change()
Call SumTxtBox
End Sub

 

[Try2Live4God]

Try this, go into design mode on each textbox and add the following code by double clicking the textbox one at time, it will use the change event.

I have created a subroutine sumtxtbox within the module sheet.

Trevor G,
Thank you for your help on this!!

I plugged the code in and nothing happens. The only thing I changed were the names for my textboxes i.e. txtCash1, txtCash2, etc. Am I missing something??

This is what I got:

Sub SumTxtBox()
On Error Resume Next
Me.txtGrandTotalCash1 = (IIf(Me.txtCash1 = "", 0, Me.txtCash1) + 0) + (IIf(Me.txtCash2 = "", 0, Me.txtCash2) + 0) + (IIf(Me.txtCash3 = "", 0, Me.txtCash3) + 0) + (IIf(Me.txtCash4 = "", 0, Me.txtCash4)
End Sub

Private Sub txtCash1_Change()
Call SumTxtBox
End Sub

Private Sub txtCash2_Change()
Call SumTxtBox
End Sub

Private Sub txtCash3_Change()
Call SumTxtBox
End Sub

Private Sub txtCash4_Change()
Call SumTxtBox
End Sub

 

[Rick Rothstein]

I plugged the code in and nothing happens. The only thing I changed were the names for my textboxes i.e. txtCash1, txtCash2, etc. Am I missing something??

Do you TextBoxes contain the words "of OT" along with the time values? If so, or even if not, try changing the SumTxtBox subroutine to this...

[table="width: 500"]
[tr]
	[td]Sub SumTxtBox()
  On Error Resume Next
  Me.txtGrandTotalCash1 = Val(Me.txtCash1) + Val(Me.txtCash2) + Val(Me.txtCash3) + Val(Me.txtCash4)
End Sub[/td]
[/tr]
[/table]

 

[Try2Live4God]

Do you TextBoxes contain the words "of OT" along with the time values? If so, or even if not, try changing the SumTxtBox subroutine to this...

[table="width: 500"]
[tr]
	[td]Sub SumTxtBox()
  On Error Resume Next
  Me.txtGrandTotalCash1 = Val(Me.txtCash1) + Val(Me.txtCash2) + Val(Me.txtCash3) + Val(Me.txtCash4)
End Sub[/td]
[/tr]
[/table]

Thank you so much Rick!! That worked.

Now here is my second overall question.......

How do I format the "Grand Total" textbox with the proper format?

Box #1 = 1:30 (One hr & thirty Minutes)
Box #2 = 1:45
Grand Total = 3:15

Right now as I have it, the Grand Total shows just "2" and is not adding the minutes.

Thank you!

 

[Rick Rothstein]

Now here is my second overall question.......

How do I format the "Grand Total" textbox with the proper format?

Box #1 = 1:30 (One hr & thirty Minutes)
Box #2 = 1:45
Grand Total = 3:15

Right now as I have it, the Grand Total shows just "2" and is not adding the minutes.

Is the only thing in your TextBoxes the time value or could there be other text in there with it (such as "of OT")?

 

[Try2Live4God]

Only number/time values with the colon )

 

[Rick Rothstein]

As per what you posted in Message #5 , assuming your five TextBoxes are named txtCash1, txtCash2, txtCash3, txtCash4 and txtGrandTotalCash1, replace the code you now have for your txtCash# TextBoxes with this...

[table="width: 500"]
[tr]
	[td]Private Sub txtCash1_Change()
  Call SumTimes
End Sub

Private Sub txtCash2_Change()
  Call SumTimes
End Sub

Private Sub txtCash3_Change()
  Call SumTimes
End Sub

Private Sub txtCash4_Change()
  Call SumTimes
End Sub[/td]
[/tr]
[/table]

and add the subroutine to at the bottom of the same module...

[table="width: 500"]
[tr]
	[td]Sub SumTimes()
  Dim T As Variant, Sum As Double
  On Error GoTo NoTotal
  For Each T In Array(txtCash1, txtCash2, txtCash3, txtCash4)
    T.BackColor = vbWhite
    If Len(T) Then Sum = Sum + CDate(T)
  Next
  txtGrandTotalCash1 = Application.Text(Sum, "[h]:mm")
  Exit Sub
NoTotal:
  T.BackColor = vbRed
  Resume Next
End Sub[/td]
[/tr]
[/table]