Hi,

Firstly, thanks alot to everyone who has already offered me help with this.
Bob Phillips, Mike H and Paul Robinson I am extremely grateful for the time
you have already given me.

Basically I am having a problem with time formatting. I have a timehseet
which has appointment start time on there. I want the time in the format
hh:mm with nothing else. When using the custom format it still included the
date so a user could not simply enter 9 for 09:00 or 1530 for 15:30 etc. Bob
kindly directed me to http://www.cpearson.com/excel/DateTimeEntry.htm which I
followed and after a little modification to the actual 'Case Formats
themselves I had the following 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
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. Please use figures only for the time
e.g. 1030"
Application.EnableEvents = True
End Sub


This works just as I want it to, a user can input any valid number string
from just one number up to four numbers i.e. 1 meaning 01:00 or 1528 for
15:28 and it is automatically formatted to hh:mm with no date. Brilliant.

However, when the more meticulous user chooses to input the time in its full
format i.e. 10:30 with the : they are presented with the error message. I
have tried multiple suggestions from the people above all of which have been
unsuccessful. Basically I want it to run exactly as it does with the script
above but I dont want it to give an error when a : is used.

I have tried adding the following:

If ActiveCell.Text Like "*:*" Then
Exit Sub
End If

------------------

TimeStr = .Value
TimeStr = CStr(Replace(TimeStr, ":", ""))

------------------

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
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, 1) & ":00"
Target.NumberFormat = "h:mm"
Case 2 ' e.g., 12 = 12:00 AM
TimeStr = TimeStr & ":00"
Target.NumberFormat = "h:mm"
Case 3 ' e.g., 123 = 1:23 AM
TimeStr = Left(TimeStr, 1) & ":" & Right(TimeStr, 2)
Target.NumberFormat = "h:mm"
Case 4 ' e.g., 1234 = 12:34 AM
TimeStr = Left(TimeStr, 2) & ":" & Right(TimeStr, 2)
Target.NumberFormat = "h:mm"
Case Else
GoTo EndMacro
End Select

.Value = TimeValue(Format(TimeStr, "HH:MM"))

End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid time"
Application.EnableEvents = True
End Sub

I am now at a loss, no matter what I have tried I get the error message
after inputting a : or the time is not entered in the correct format so one
problem has been solved but another created with the format. I am loathed to
simply alter the error message to tell the user not to use the : and equally
I do not want to use On Error Resume Next.

I apologise for the length of this post but I think the situation needed
fully explaining. I am at a complete loss as to how to make this work and am
at my wits end. Thank you in advance for any help you can offer. If I have
not explained clearly enough just reply asking for more specific details.