OK, I'm starting to think that the IF method may work.

Doing a little bit of tweaking I have added an IF statement which is:

If Target.Value = "1000" Then
Exit Sub
End If

If I enter 1000 as if trying to enter 10:00 then nothing is done to the
format and it is entered as a date (which it should be in this case). I have
tried using wildcards to simulate ##:## so that if numbers were entered, then
a colon, then numbers it would ignore the rest of the code in the way it does
with the 1000 example. If the cells are set to Format, Custom, hh:mm and the
: is entered then the time remains in the correct format so it is ok for the
code to be ignored in this case.

Is it possible to make these wildcard characters work in this statement? I
have tried looking on the help but there is no examples etc of how it should
be coded.

"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.