Hi,

the line
TimeStr = CStr(Replace(TimeStr, ":", ""))
won't remove the colon which is what the other poster suggested so
try this. Not extensively tested so It may not be bulletproof but seems to
work. Note the extra 'Exit Sub' 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
'***

If Target.NumberFormat = "h:mm" Then
Target.NumberFormat = "h:mm AM/PM"
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
GoTo EndMacro
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


Mike


"Pyrite" wrote:

Hi,

With some help today (thanks a lot Paul Robinson) I have used some code on a
Worksheet Change to
ensure that time is being entered into my spreadsheet in the correct format.
So far it all works brilliantly and all time is displayed as hh:mm no matter
what is entered whether it be a full 1030 or just 9 etc. The only problem I
have is that if the user chooses to put the : in themselves (which some will)
it returns a 'Enter A Valid Time' error. Paul helped add the Replace
statement to try and take the : out of play but it still isnt working. Could
anyone else offer any suggestions?

Basically if you enter 09:30 you get the error message but then 09:30
remains in the cell in the correct format. I could leave it like this but
when it goes to the end users I will get hundreds of phone calls because they
are getting an error even though the end result is still correct. I could
just use On Error Resume Next but I think this would be a little lazy.

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

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