Entering Time with the right format in Text Box

[eddyrcabrera79]

Hi,

I’m working with a Userform that transfer information into an excel sheet. One of the Textbox is called (Start Time). When I click on it, it will ask me to enter the time. This is the help I require.

1 – How can I make the colon ) symbol to populate by itself. Ex: I would like to type 400 and it will populate 4:00

2 – I will also have to type if it is AM or PM. I would like to type the time: Ex – 400pm and I would like to be populated like this 4:00 AM (There will have to be a space after the time and the am or pm will have to change to UPPERCASE

Please see below the code I’m using at the moment

Private Sub StartTimeTextBox_Enter()
Dim MyInput As Variant

MyInput = Application.InputBox("hh:mm AM/PM (12 Hour Format)", "Please enter the Start time")
MyVal = MyInput

If MyInput = Format(MyVal, "hh:mm AM/PM") And _
Mid(MyInput, 4, 2) <= 59 And _
Mid(MyInput, 4, 2) >= 0 And _
Left(MyInput, 2) >= 1 And _
Left(MyInput, 2) <= 12 Then
    StartTimeTextBox.Value = MyInput
    ElseIf _
Mid(MyInput, 4, 2) <= 59 And _
Mid(MyInput, 4, 2) >= 0 And _
Left(MyInput, 1) >= 1 And _
Left(MyInput, 1) <= 9 Then
    StartTimeTextBox.Value = MyInput
Else
    MsgBox "Time Format is incorrect" & vbNewLine & "Please ensure it is in hh:mm AM/PM", vbCritical, ""
End If
End Sub

Regards,

 

[Chihiro]

My suggestion, split textbox into component parts.
StartHour, StartMin and dropdown for AM/PM.

Also add Label between StartHour & StartMin with ":" as caption.

Limit StartHour to numbers between 0-12.
Limit StartMin to numbers between 0-60.

Then concatenate info for output.

Edit: StartMin should be between 0-59.

 

[eddyrcabrera79]

Hi Chihiro,

Than for the help. But I don't understand as I'm not a VBA savvy. I attached the excel doc in case you can update the code.

Could you please put comments on it, in order for me to see the update and have an idea for next time

Thank you

 

[Chihiro]

Sorry. I'm bit busy next couple of days. Will see if I have time tomorrow evening.

 

[eddyrcabrera79]

np Chihiro, I understand

 

[eddyrcabrera79]

Hi Chihiro,


If for some reason you are able to work on it when you have time. When I click on the text box it doesn't have to pop up a new windows to enter the time, for this case it would be better. I can enter the time in the textbox, the below is the important part for me.


1 - I would like to type the time in this format EX: 400pm or 1100am. After this will have to be changed automatically to 4:00 PM or 11:00 AM (it must put automatically the colon, the space and the am or pm uppercase.

2 - The hour should be only from 1 to 12 and the minutes from 00 to 59. If for some reason there is a mistake with the time entered because someone type for example 13:63 I would like to receive a message requesting to check the format.


Thank you in advance

 

[eddyrcabrera79]

Hello All,

The below code was provided to me and it works perfect. Just it needs to pasted in the textbox and change the textbox name in the code.

Enjoy

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    Dim Entry As String
    Entry = TextBox1.Text
   
    Entry = LCase(Entry)
    Entry = Replace(Entry, " ", vbNullString)
    If Entry Like "*[ap]" Then Entry = Entry & "m"
    If Entry Like "###[ap]m" Then Entry = WorksheetFunction.Replace(Entry, 2, 0, ":")
    If Entry Like "####[ap]m" Then Entry = WorksheetFunction.Replace(Entry, 3, 0, ":")
   
    If IsDate(Entry) Then
        TextBox1.Text = Format(TimeValue(Entry), "h:mm AM/PM")
    Else
        MsgBox "wrong timeformat"
        Cancel = True
    End If
End Sub