Mike,

This worked up until I entered a time with the : in place. After that if you
input just 9 for instance it reverts back to using that number to form a date
and will then only accept times with the : in place.

Would there be a way to add to the IF statements at the beginning to say
that If Target.Value CONTAINS ":" Then Exit Sub?? It seems as if then when
the user inputs : the sub would not run. I really am grasping at straws at
this point I know and I have no idea if this is possible or not.

My final alternatives at the minute are to change the error message to read
"Please enter a valid time using only numerical characters e.g. 1030" or to
use the dreaded On Error Resume Next. Both of these seem a little lazy
though. Ultimately the entered : either needs ignoring or replacing.........I
think.

Thanks again for all your help Mike, it really is appreciated.

Its a shame this is proving so problematic, this is the first Worksheet
Change I have ever done and upon reading and implementing it I was very
impressed. Now, every time I see "Please enter a valid time" because I have
used the : I want to scream :-)

"Mike H" wrote:

Hi,

More extensively tested

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


Mike

"Pyrite" wrote:

Thanks for your help Mike, unfortunately this still doesnt do the trick. I
have found that it adds AM/PM when I would rather stick to the 24 hour clock.
It also in a number of cases didnt add the time, it put the date before it
which was my original problem that led me to the Worksheet Change programming.

This is proving to be really hard work. Thanks for helping though. Please
dont think I am just squeezing this forum for info and sitting around doing
nothing, I have limited skill but have spent several hours today trying to
alter the advice given to suit the outcome required.

The closest I have been so far was with my original code which was:


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"
Application.EnableEvents = True
End Sub


This is how I want it to work, the only problem here is the input of the :
by the user. I understand that you are busy and really appreciate your help,
I think if you copy and paste this code you will see exactly what I am trying
to achieve. When you have copied and pasted if you change the format of cells
F8 to F51 (or just some of them to test) to Custom, hh:mm you will see
exactly how I want it to work. You can enter just the number 9 or 1027 or 134
or any string of legitimate numbers and it gets formatted to a time in the
format hh:mm. The only single problem is if you try to enter the time as
09:00 for instance. I am really sorry if this makes me sound like I'm being a
pain or something, I dont question your understanding at all, just my
explanation.