Hi Stefi

I hadn't meant my suggestion about the 60 ending to sound critical.
I realise that the original code didn't cater for it, I was just
suggesting a further addition to the excellent work you had already done.

--
Regards
Roger Govier

Stefi wrote:
Hi Roger,

Thanks for the appreciation. As far as 60 ending is concerned, the original
code also missed to handle this issue even with hour numbers less then 24,
and the OP didn't include it, I focused on the written request.

Nevertheless I consolidated suggestions from you and Dave, this is the final
form, though I guess that the Select Case code could be written in a more
compact way.


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TimeStr As String

On Error GoTo EndMacro
If Application.Intersect(Target, _
Me.Range("E:E,g:g,k:k,m:m,o:o,q:q,s:s,u:u,w:w,y:y" )) Is Nothing Then
Exit Sub
End If

If Target.Cells.Count 1 Then
Exit Sub
End If
If Target.Value = "" Then
Exit Sub
End If

Application.EnableEvents = False

If Right(Target.Value, 2) = "60" Then
MsgBox "Time entered cannot end in 60 (only 59)!"
Target.Value = ""
Target.Activate
Application.EnableEvents = True
Exit Sub
End If

With Target
If .HasFormula = False Then
dayno = 0
Select Case Len(.Value)
Case 1 ' e.g., 1 = 00:01 AM
TimeStr = "00:0" & .Value
Case 2 ' e.g., 12 = 00:12 AM
TimeStr = "00:" & .Value
Case 3 ' e.g., 735 = 7:35 AM
TimeStr = Left(.Value, 1) & ":" & _
Right(.Value, 2)
Case 4 ' e.g., 1234 = 12:34
hourno = Val(Left(.Value, 2))
dayno = hourno \ 24
remhour = hourno Mod 24
TimeStr = Str(remhour) & ":" & _
Right(.Value, 2)
Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45
hourno = Val(Left(.Value, 1))
dayno = hourno \ 24
remhour = hourno Mod 24
TimeStr = Str(remhour) & ":" & _
Right(.Value, 2)
Case 6 ' e.g., 123456 = 12:34:56
hourno = Val(Left(.Value, 2))
dayno = hourno \ 24
remhour = hourno Mod 24
TimeStr = Str(remhour) & ":" & _
Right(.Value, 2)
Case Else
Err.Raise 0
End Select
.Value = TimeValue(TimeStr) + dayno
.NumberFormat = "[h]:mm:ss"
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid time"
Application.EnableEvents = True
End Sub