Time quirk

[normpam]

The code below works just fine. If someone enters '915' they will get 9:15am, or '1345' and get 1:30pm. But it seems two times never work.... 12:00pm which results in 0:.5 and
1648 which results in 0:.7

Any ideas?

Sub Auto_Open()
    Call SetUp_OnEntry
End Sub

Sub SetUp_OnEntry()
    Application.OnEntry = "SetColon"
End Sub


Sub SetColon()
    If ActiveCell.Column = 3 Or ActiveCell.Column = 2 Or ActiveCell.Column = 4 Or ActiveCell.Column = 5 Then
    If Len(ActiveCell.Value) = 4 Then ActiveCell.Value = Left(ActiveCell.Value, 2) & ":" & Right(ActiveCell.Value, 2)
    If Len(ActiveCell.Value) = 3 Then ActiveCell.Value = Left(ActiveCell.Value, 1) & ":" & Right(ActiveCell.Value, 2)
    End If
End Sub

 

[offthelip]

those values work perfectly Ok with my version of excel. I was testing it using this code:

Sub SetColon()
If Len(ActiveCell.Value) = 4 Then tt = Left(ActiveCell.Value, 2) & ":" & Right(ActiveCell.Value, 2)
If Len(ActiveCell.Value) = 3 Then tt = Left(ActiveCell.Value, 1) & ":" & Right(ActiveCell.Value, 2)
ActiveCell.Offset(1, 0) = tt
MsgBox "tt " & tt

End Sub

Your reference to application.onentry is something that has been superceded by the worksheetchange event , so I am wondering what version of EXCEL you are using, can you update your profile to specify which version of excel you are using because the anwer may depend on which verson of excel you have

 

[normpam]

I actually wrote this code probably about 20 years ago!

 

[offthelip]

I was also using Excel over 20 years ago, but I can't remember all the quirks about the syntax of those old versions, so unless you are are still using Excel 97 or something, what about telling what version you are using by updating your profile. Then somebody might be able to suggest a rewrite to use some new millenium techonology!! It might solve your problem!! ( Actually if you are using Excel 97, this is even more important!!)

 

[Marc L]

Whatever the Excel version it should be like​

Sub SetColon()
        Dim L&
    With ActiveCell
        L = Len(.Value)
        If .Column > 1 And .Column < 6 And IsNumeric(.Value) And L > 2 And L < 5 Then _
           .Value = Left(.Value, L - 2) & ":" & Right(.Value, 2)
    End With
End Sub

 

[Marc L]

As the glitch occurs when the procedure is launched on a cell already formatted as time so this is the update :​

Sub SetColon()
        Dim L&
    With ActiveCell
        L = Len(.Value)
        If .Column > 1 And .Column < 6 And IsNumeric(.Text) And L > 2 And L < 5 Then _
           .Value = Left(.Value, L - 2) & ":" & Right(.Value, 2)
    End With
End Sub

​

According to Excel behavior / rules with a cell already formatted as time (date) the user must enter the colon character …​

 

[Marc L]

The good way to avoid the glitch (Len trap !) if the cell is already formatted as time :​

Sub SetColon()
        Dim L&
    With ActiveCell
        L = Len(.Text)
        If .Column > 1 And .Column < 6 And IsNumeric(.Value) And L > 2 And L < 5 Then _
           .Value = Left(.Value, L - 2) & ":" & Right(.Value, 2)
    End With
End Sub

​

 

[normpam]

The good way to avoid the glitch (Len trap !) if the cell is already formatted as time :​

Sub SetColon()
        Dim L&
    With ActiveCell
        L = Len(.Text)
        If .Column > 1 And .Column < 6 And IsNumeric(.Value) And L > 2 And L < 5 Then _
           .Value = Left(.Value, L - 2) & ":" & Right(.Value, 2)
    End With
End Sub

​

Thanks.... will need to study this a bit...

 

[normpam]

Why would it only happen with 1648 (4:48pm) and 12:00 noon and no other times?

 

[Marc L]

​

As already explained 'cause of the Len trap like you must read within its VBA help and it could happen with some other times …​