Hmm,

I have copied this code from scratch to avoid missing any of the .Value to
TimeStr changes but it is still giving the same result when a time is entered
with the : in place. You have not entered a valid time

Thanks again for all your help.

" wrote:

Hi
Try this code

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


'On Error GoTo EndMacro
If Application.Intersect(Target, Range("F8:F51")) 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
With Target
TimeStr = .Value
TimeStr = CStr(Replace(TimeStr, ":", ""))

If .HasFormula = False Then
Select Case Len(TimeStr)
Case 1 ' e.g., 1 = 01:00 AM
TimeStr = Left(TimeStr, 2) & ":00"
Case 2 ' e.g., 12 = 12:00 AM
TimeStr = TimeStr & ":00"
Case 3 ' e.g., 123 = 1:23 AM
TimeStr = Left(TimeStr, 1) & ":" & _
Right(TimeStr, 2)
Case 4 ' e.g., 1234 = 12:34 AM
TimeStr = Left(TimeStr, 2) & ":" & _
Right(TimeStr, 2)
Case Else
Err.Raise 0
End Select
.Value = TimeValue(TimeStr)
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid time"
Application.EnableEvents = True
End Sub

I was mixing up TimeStr and Target.Value in the code. Seems to work
now.

regards
Paul

On Sep 9, 10:07 am, Pyrite wrote:
Paul,

Thank you very much for your rapid repsonse.

However, after making the change I am still having the same problem. If I
enter the time as 09:00 for instance the time is enterd into the cell in the
correct format but the error message appears, after clicking ok the time is
left in the cell in the correct format. I could leave it as is but as you no
doubt know I will get a million and one phone calls when the timesheet goes
to the end users. The strange thing is I can enter the time as 09.00 and it
just changes it to 09:00 no complaining. I dont understand how it can say the
time is incorrect when it is in the exact format it is changing it to.



" wrote:
Hi
Put in your line before the select case and call it TimeStr (see code
below). Now your select case has a string in the right format to work
on.
Not tested.

Application.EnableEvents = False
With Target
TimeStr= CStr (Replace(.Value, ":", ""))
If .HasFormula = False Then
Select Case Len(.Value)
Case 1 ' e.g., 1 = 01:00 AM
TimeStr = Left(.Value, 2) & ":00"
Case 2 ' e.g., 12 = 12:00 AM
TimeStr = .Value & ":00"
Case 3 ' e.g., 123 = 1:23 AM
TimeStr = Left(.Value, 1) & ":" & _
Right(.Value, 2)
Case 4 ' e.g., 1234 = 12:34 AM
TimeStr = Left(.Value, 2) & ":" & _
Right(.Value, 2)
Case Else
Err.Raise 0
End Select
.Value = TimeValue(TimeStr)
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid time"
Application.EnableEvents = True
End Sub

regards
Paul- Hide quoted text -

- Show quoted text -