• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Userform textbox - Time format "HH:MM" problem

Villalobos

Active Member
Hello,

I would need some help to solve a time format ("HH:MM") problem in textbox (on userform). My target is that after that the user typed starting time into textbox and left that then the format should be: HH:MM. This is the code what I have now but doesn't work as I would need.

Code:
Private Sub TextBox1_Afterupdate()
    TextBox1.Value = Format(TextBox1.Value, "HH:MM")
End Sub

Do you have any idea?

The sample file has been added to this thread.



Thanks in advance!
 

Attachments

  • sample.xlsm
    16.5 KB · Views: 104
Seems to be working ok for me.
What I type:
upload_2014-11-20_13-33-28.png
What it becomes:
upload_2014-11-20_13-33-42.png
Are you typing something different into the form?
 
Then you'll need to have the code figure out how to split the number from being an integer (aka, a date) into decimals (aka, a time).
Code:
Private Sub TextBox1_Afterupdate()
Dim tString As String
With TextBox1
    'Check if user put in a colon or not
    If InStr(1, .Value, ":", vbTextCompare) = 0 Then
        'If not, make string 4 digits and insert colon
        tString = Format(.Value, "0000")
        tString = Left(tString, 2) & ":" & Right(tString, 2)
       
        TextBox1.Value = Format(TimeValue(tString), "HH:MM")
    Else
        'Otherwise, take value as given
        .Value = Format(.Value, "hh:mm")
    End If
End With
End Sub
 
Then you'll need to have the code figure out how to split the number from being an integer (aka, a date) into decimals (aka, a time).
Code:
Private Sub TextBox1_Afterupdate()
Dim tString As String
With TextBox1
    'Check if user put in a colon or not
    If InStr(1, .Value, ":", vbTextCompare) = 0 Then
        'If not, make string 4 digits and insert colon
        tString = Format(.Value, "0000")
        tString = Left(tString, 2) & ":" & Right(tString, 2)
     
        TextBox1.Value = Format(TimeValue(tString), "HH:MM")
    Else
        'Otherwise, take value as given
        .Value = Format(.Value, "hh:mm")
    End If
End With
End Sub

Hi Luke M and all

This helped me as well - thank you!

May I ask for your input on the following:

if a user enters a value that does NOT fit into the HH:MM format parameters, such as typing an hour value of more than 23, or a minute value of more than 59, that a msgBox is displayed giving the user a warning, instead of the Run-time error '13'.

Here is my code:

Code:
'TIME FORMAT
Private Sub txtShiftStart_Afterupdate()
Dim tString As String
With txtShiftStart
    'Check if user put in a colon or not
  If InStr(1, .Value, ":", vbTextCompare) = 0 Then
        'If not, make string 4 digits and insert colon
      tString = Format(.Value, "0000")
        tString = Left(tString, 2) & ":" & Right(tString, 2)
     
        txtShiftStart.Value = Format(TimeValue(tString), "HH:MM")
    Else
        'Otherwise, take value as given
      .Value = Format(.Value, "hh:mm")
    End If
End With
End Sub

Thank you in advance!
 
Hello onmyway,

In the future, please start a new thread (with link to reference thread, if applicable), rather than tacking on to an old one. Otherwise, your post may get ignored as it's on an older thread that appears to have been solved already.

But anyway, here's how you can solve the issue. I probably should have gone this route in the first place, but I forgot about the TimeSerial method.
Code:
Private Sub txtShiftStart_Afterupdate()
Dim tString As String
Dim tDate As Date
On Error GoTo ErrMsg
With txtShiftStart
    'Check if user put in a colon or not
    If InStr(1, .Value, ":", vbTextCompare) = 0 Then
        'If not, make string 4 digits and insert colon
        tString = Format(.Value, "0000")
        tDate = TimeSerial(Left(tString, 2), Right(tString, 2), 0)
       
        txtShiftStart.Value = Format(tDate, "HH:MM")
    Else
        'Otherwise, take value as given
        .Value = Format(.Value, "hh:mm")
    End If
End With
Exit Sub
ErrMsg:
MsgBox "Oops! You typed in something we weren't expecting.", vbOKOnly, "Unexpected entry"
End Sub
 
Language=Persian
بهتره اگر طول تکس باکس کمتر از 2 باشد برای جلوگیری از خطا اینگونه تصحیح شود
Private Sub TextBoxEndTime_AfterUpdate()
Dim tString As String
With TextBoxEndTime
'Check if user put in a colon or not
If InStr(1, .Value, ":", vbTextCompare) = 0 And Len(.Value) > 1 Then
'If not, make string 4 digits and insert colon
tString = Format(.Value, "0000")
tString = Left(tString, 2) & ":" & Right(tString, 2)
TextBoxEndTime.Value = Format(TimeValue(tString), "hh:mm")​
Else
'Otherwise, take value as given
.Value = Format(.Value, "hh:mm")​
End If​
End With​
End Sub
 
Last edited by a moderator:
When I use the code above, I am getting an error message "expected expression" on this line:

If InStr(1, .Value, ":", vbTextCompare) = 0 Then

What am I doing wrong?
 
Hi, Taxgirl!
You surely has a textbox control named TextBoxEndTime, I guess... Don't you?
If not, please go to the top of this post and verify that your workbook has a proper userform with the required text boxes.
Regards!
 
Back
Top