VBA insert colon between numbers not working

tryingcake · Jun 25, 2011

[FONT=&quot]This thread is a spin off of this thread:

http://www.mrexcel.com/forum/showthread.php?t=541671

I am using the following code to be able to enter time quickly and easily:
[/FONT]
Private Sub Worksheet_Change(ByVal Target As Range)
UserInput = Target.Value
If UserInput > 1 Then
NewInput = Left(UserInput, Len(UserInput) - 2) & ":" & Right(UserInput, 2)
Application.EnableEvents = False
Target = NewInput
Application.EnableEvents = True
End If
End Sub</pre>
[FONT=&quot]It works the day I create it. But when I shut down and go back to it, all i get is 00:00. It wants to put the date in it.

Here is a snapshot of the screen ([/FONT]I can't get the picture to load. Here is the link):

http://flic.kr/p/9VjNRz

I did have a range in there, but removed in hopes that would fix things. It didn't.
[FONT=&quot]
No, there is not any other code. I copy and pasted directly from here:

http://www.mrexcel.com/articles/enter-time-without-colon.php

I have a formula in another cell directing it to give me the total hour duration (a simple f-e= x hours). This isn't shown. I added after the snapping the screen. In the next cell I have it multiplying the hours worked by the wage due to the employee. (This is for a cash job and so I can pay everyone right away). I would like for them to be able to "clock In" and clock out."

Thank you.[/FONT]

taurean · Jun 25, 2011

Please check the format where 00:00 shows up. The entry seems to be changed to date format. Possibly because an entry was made with this "/" character.

AlphaFrog · Jun 25, 2011

Try something like this...

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim TimeTest As Single
    
    If Target.Count = 1 Then
        If Target.Column = 3 Or Target.Column = 4 Then      ' For columns C or D only
            If Target <> "" Then                            ' Ignore when entry is deleted
            
                'Test if user entered a recognized time format e.g. "12:30" or "12:30 PM"
                On Error Resume Next
                TimeTest = TimeValue(Target.Text)
                On Error GoTo 0
                
                If TimeTest = 0 Then
                    'Entry is not a recognized time
                    'Try to Convert 4 digits to time
                    On Error Resume Next
                        TimeTest = TimeValue(Left(Target, Len(Target) - 2) & ":" & Right(Target, 2) & ":00")
                    On Error GoTo 0
                    
                    If TimeTest = 0 Then
                        'Cannot convert entry to time
                        'Clear and notify
                        Application.EnableEvents = False
                            Target.ClearContents
                        Application.EnableEvents = True
                        MsgBox "Invalid time entry." & vbLf & "Please try again.", vbExclamation, "Invalid Entry"
                    Else
                        ' Entry converted. Apply time.
                        Application.EnableEvents = False
                            Target = TimeTest
                        Application.EnableEvents = True
                    End If
                End If
            End If
        End If
    End If
    
End Sub

tryingcake · Jun 26, 2011

taurean said:
Please check the format where 00:00 shows up. The entry seems to be changed to date format. Possibly because an entry was made with this "/" character.

I have the simplest time format chosen. 13:30. I have even tried custom format of hh:mm.

I don't enter any dashes or slashes. if it is 5:42 pm I enter 1742.

tryingcake · Jun 26, 2011

AlphaFrog, I tried it - didn't work. I'll start fresh with a new worksheet and see if that helps. I'll let you know. Thanks!!

mikerickson · Jun 26, 2011

Perhaps

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim strTime As String
    With Target
        If .Cells.Count = 1 Then
            If IsNumeric(.Value) Then
                On Error GoTo ErrorOut
                Application.EnableEvents = False
                strTime = Format(Val(.Value), "0000")
                strTime = Left(strTime, Len(strTime) - 2) & ":" & Right(strTime, 2)
                If IsDate(strTime) Then
                    .Value = TimeValue(strTime)
                End If
            End If
        End If
    End With
ErrorOut:
    Application.EnableEvents = True
End Sub

VBA insert colon between numbers not working

tryingcake

New Member

Excel Facts

taurean

Well-known Member

AlphaFrog

MrExcel MVP

tryingcake

New Member

tryingcake

New Member

mikerickson

MrExcel MVP

Similar threads

Forum statistics

Share this page

VBA insert colon between numbers not working

tryingcake

New Member

Excel Facts

taurean

Well-known Member

AlphaFrog

MrExcel MVP

tryingcake

New Member

tryingcake

New Member

mikerickson

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock