Summing textboxes on userform

[rhombus4]

Trying to add 3 textboxes on a userform and restrict them to only accept hh:mm

Txtttl = Val(txt1.text) + Val(txt2.text) Val(txt3.text)

Also I want the txtttl to be formatted as decimal when txt1 txt2 and txt3 are added together

 

[rhombus4]

Not sure if I explained it properly in OP

Trying to restrict a Userform textbox to only accept 8:12 or 08:12 and if someone enters 8.5 or 8.50 it will convert to 8:30

This is what I have so far.

If I enter 5:30 in txt1, txt2 or txt3 the txtTotal shows 0:00
If I enter 5.30 the txtTotal shows 5.03

I want it to Add up the Hours in the 2 text boxes and show the Value as HH:MM in the txtTotal textbox

e.g. If I enter 5:30 6:10 and 4:05 it will show 15:45
Similarly if I enter 2.5 3.5 and 4.25 the 3 textboxes will show 2:30 3:30 and 4:15 and the txtTotal will be 10:15

Private Sub txt1_Change()
Add_TextBoxes
End Sub

Private Sub txt2_Change()
Add_TextBoxes
End Sub

Private Sub txt3_Change()
Add_TextBoxes
End Sub

Private Sub Add_TextBoxes()
Me.txtTotal = Val(txt1.Text) + Val(txt2.Text) + Val(txt3.Text)
Me.txtTotal.Value = Format((Me.txtTotal.Value), "HH:MM")

End Sub

 

[rhombus4]

When I type normal numbers i.e. 1 2 and 3 in the 3 textboxes I still get 00:00 in the txtTotal textbox
.
But If I remove the line Me.txtTotal.Value = Format((Me.txtTotal.Value), "HH:MM")

it will Show 6.

Any ideas ??

 

[dmt32]

Any ideas ??

The Val function recognizes only the period ( . ) as a valid decimal separator.

When coercing a Time string, try using either CDate or TimeValue function but do be aware, that error handling will be required for these functions to manage any invalid values.



Dave

 

[rhombus4]

The Val function recognizes only the period ( . ) as a valid decimal separator.

When coercing a Time string, try using either CDate or TimeValue function but do be aware, that error handling will be required for these functions to manage any invalid values.



Dave

Hi Dave
They both seem to concatenate the values

When I enter 1:30 in 1st box and then 2:45 in 2nd Box total showed
01:3002:45 like a concatenate

and if I have 3 Textboxes to add and the 3rd is Blank it Won't do anything as error is ignored


CDATE

On Error Resume Next
Me.txtTotal.Value = Format(CDate(Me.txt1.Value), "HH:MM") + Format(CDate(Me.txt2.Value), "HH:MM") + Format(CDate(Me.txt3.Value), "HH:MM")

TimeValue

On Error Resume Next
Me.txtTotal.Value = Format(TimeValue(Me.txt1.Value), "HH:MM") + Format(TimeValue(Me.txt2.Value), "HH:MM")

 

[dmt32]

Add the Date / Time values first & then perform the Format operation .

On Error Resume Next
Me.txtTotal.Value = CDate(Me.Txt1.Value) + CDate(Me.Txt2.Value) + CDate(Me.Txt3.Value)

Me.txtTotal.Value = Format(CDate(Me.txtTotal.Value), "hh:mm")

Dave

 

[rhombus4]

Thanks
So now I have code below

Is there anyway to add them if one of the textboxes contains Text i.e. Absent
and Restrict them to either hh:mm or Absent

Private Sub txt1_Change()
Add_TextBoxes
End Sub

Private Sub txt2_Change()
Add_TextBoxes
End Sub

Private Sub txt3_Change()
Add_TextBoxes

End Sub
Private Sub Add_TextBoxes()
On Error Resume Next
Me.txtTotal.Value = CDate(Me.txt1.Value) + CDate(Me.txt2.Value) + CDate(Me.txt3.Value)
Me.txtTotal.Value = Format(CDate(Me.txtTotal.Value), "hh:mm")

End Sub

 

[dmt32]

you can certainly restrict manual entry entry to numeric only (with valid time separator)

try add these codes to your form

Private Sub Txt1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
'allow numeric [0-9] : only
    KeyAscii = NumbersOnly(KeyAscii)
End Sub

Private Sub Txt2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
'allow numeric [0-9] : only
    KeyAscii = NumbersOnly(KeyAscii)
End Sub

Private Sub Txt3_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
'allow numeric [0-9] : only
    KeyAscii = NumbersOnly(KeyAscii)
End Sub

Function NumbersOnly(ByVal KeyAscii As MSForms.ReturnInteger) As MSForms.ReturnInteger
    Select Case KeyAscii
    Case 48 To 58
    'valid entries [0-9] ":"
    Case Else
    'invalid entry
        KeyAscii = 0
    End Select
    Set NumbersOnly = KeyAscii
End Function

but this will EXCLUDE all other direct text value entries.

Dave

 

[rhombus4]

Thanks Dave

Was hoping for easier way to add textboxes, even if one contained text

 

[Akuini]

@rhombus4
My suggestion:
Your code uses textbox change event to run Sub Add_TextBoxes().
It would be esier if you run Sub Add_TextBoxes by clicking a command button. This way you can validate the textboxes entry on Exit event, not in every keypress, so restricting them to either hh:mm or Absent would be simpler to code.
Let me know if you're interested in this approach.