TextBox to time

[Patrick C. Simonds]

Is there any way, as you exit a textbox, the value displayed in the
TextBox could be changed to the time format hh:mm (24 hour)?

So if someone entered 800 in textbox1, when they exit the textbox, textbox1
would display 08:00, but if they entered 08:00 the format would not be
changed.

 

[Bob Phillips]

You can trap the input to ensure they do input as time

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
With Me.TextBox1

Select Case True

Case Len(.Text) = 2 And KeyAscii = 58 'ok
Case Len(.Text) = 5 And KeyAscii = 58 'ok
Case Len(.Text) <= 2 And KeyAscii >= 48 And KeyAscii <= 57 'ok
Case (Len(.Text) = 3 Or Len(.Text) = 4) And KeyAscii >= 48 And
KeyAscii <= 57 'ok
Case (Len(.Text) = 6 Or Len(.Text) = 7) And KeyAscii >= 48 And
KeyAscii <= 57 'ok
Case Else: KeyAscii = 0
End Select
End With
End Sub

this could be extended to only allow w4 hours, 60 mins, 60 secs

 

[Patrick C. Simonds]

This did not work. But it also is not exactly what I am looking for.

I want to allow them to enter the time as 800 or 0800 and when they exit the
textbox have the inputted value be changed to (and displayed in the textbox
as) 08:00.

I need this to happen before the result is put on the worksheet because I
want to use the values of the textbox's to calculate a result that is
displayed on the UserForm.

 

[Bob Phillips]

Okay, you could try

Private Sub TextBox1_AfterUpdate()

TextBox1.Text = Format(Application.Text(Replace(TextBox1.Text, ":", ""),
"00\:00"), "hh:mm")
End Sub

but this is flawed, they could enter 1761 for instance

 

[Patrick C. Simonds]

Thank you

That work great. As to your other comment. They are pretty use to entering
the times (they just were complaining about having to enter the : ) But
you are correct they could mistype a number. Not sure how I will deal with
that problem. I guess I will see if it becomes an issue.

 

[Patrick C. Simonds]

Sorry, but I am still having a problem. The code you gave me did exactly
what I asked it to do, but when my TextBox12_Exit code runs (see cod below),
I get a Type Mismatch error.



Private Sub TextBox11_AfterUpdate()

TextBox11.Value = Format(Application.Text(Replace(TextBox11.Value, ":",
""), "00\:00"), "hh:mm")

End Sub


Private Sub TextBox12_AfterUpdate()

TextBox12.Value = Format(Application.Text(Replace(TextBox12.Value, ":",
""), "00\:00"), "hh:mm")

End Sub



Private Sub TextBox12_Exit(ByVal Cancel As MSForms.ReturnBoolean)

TextBox13.Text = (TextBox12.Value - TextBox11.Value) * 24

End Sub

 

[Bob Phillips]

Private Sub TextBox11_AfterUpdate()

TextBox11.Value = Format(Application.Text(Replace(TextBox11.Value, ":",
""), "00\:00"), "hh:mm")

End Sub

Private Sub TextBox12_AfterUpdate()

TextBox12.Value = Format(Application.Text(Replace(TextBox12.Value, ":",
""), "00\:00"), "hh:mm")

End Sub

Private Sub TextBox12_Exit(ByVal Cancel As MSForms.ReturnBoolean)

TextBox13.Text = (TimeValue(TextBox12.Value) -
TimeValue(TextBox11.Text)) * 24

End Sub

 

[Patrick C. Simonds]

Thank you for taking the time to help.

Private Sub TextBox11_AfterUpdate()

TextBox11.Value = Format(Application.Text(Replace(TextBox11.Value, ":",
""), "00\:00"), "hh:mm")

End Sub

Private Sub TextBox12_AfterUpdate()

TextBox12.Value = Format(Application.Text(Replace(TextBox12.Value, ":",
""), "00\:00"), "hh:mm")

End Sub

Private Sub TextBox12_Exit(ByVal Cancel As MSForms.ReturnBoolean)

TextBox13.Text = (TimeValue(TextBox12.Value) -
TimeValue(TextBox11.Text)) * 24

End Sub


--
__________________________________
HTH

Bob