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.

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

--
__________________________________
HTH

Bob

"Patrick C. Simonds" wrote in message
...
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.

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" wrote in message
...
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

--
__________________________________
HTH

Bob

"Patrick C. Simonds" wrote in message
...
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.

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

--
__________________________________
HTH

Bob

"Patrick C. Simonds" wrote in message
...
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" wrote in message
...
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

--
__________________________________
HTH

Bob

"Patrick C. Simonds" wrote in message
...
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.

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.


"Bob Phillips" wrote in message
...
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

--
__________________________________
HTH

Bob

"Patrick C. Simonds" wrote in message
...
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" wrote in message
...
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

--
__________________________________
HTH

Bob

"Patrick C. Simonds" wrote in message
...
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.

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