Formating a number in a textbox

[Patrick C. Simonds]

At work we use the 24 time format.

What I would like is something that will format time being inputted to a
textbox (TextBox1) to 00:00. So if they enter 1347 it will be converted to
13:47.

 

[Joel]

Read the data from the text box into a varuiable and then use FORMAT()
function to get the 24 hour time and then put it back into the box

Mytime = textbox1.valuse
Mytime = format(Mytime,"H:MM")

 

[Dave Peterson]

I created a small userform with a textbox, a label (for error messages) and two
commandbuttons (ok/cancel).

This seemed to work ok:

Option Explicit
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim myTime As Variant
Dim myStr As String
Dim okTime As Boolean

myStr = Me.TextBox1.Value

okTime = True

If IsNumeric(myStr) = False Then
'check for a time entered by the user
'like 12:34
myTime = ""
On Error Resume Next
myTime = TimeValue(myStr)
On Error GoTo 0
If myTime = "" Then
'not a time
okTime = False
Else
myStr = Format(myTime, "hhmm")
End If
End If

If okTime = False Then
'skip this check
Else
myTime = ""
On Error Resume Next
myTime = TimeValue(Format(myStr, "00:00"))
On Error GoTo 0

If Format(myTime, "hhmm") = Format(myStr, "0000") Then
'ok
Else
okTime = False
End If
End If

If okTime Then
Me.TextBox1.Value = Format(myTime, "hh:mm")
Me.Label1.Caption = ""
Else
Cancel = True
Beep
Me.Label1.Caption = "Please enter a time"
End If

End Sub
Private Sub UserForm_Initialize()

Me.Label1.Caption = ""

With Me.CommandButton1
.Caption = "Ok"
.Default = True
End With

With Me.CommandButton2
.Caption = "Cancel"
.Cancel = True
.TakeFocusOnClick = False
End With

End Sub


Notice that this uses the _exit event. This event fires when you try to leave
the textbox--not after each character is entered.