I have tested this with quite a few values, and many repetitions, and it
seems to stand up. It includes the formatting you want
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
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
With Target
If .HasFormula = False Then
If .Value = 1 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
.NumberFormat = "hh:mm"
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
--
__________________________________
HTH
Bob
"Pyrite" wrote in message
...
Bob,
Upon further testing I have found the code to be a little temperamental.
The
first 3 times that you enter work perfectly, no matter what you enter, I
tried 10, 827 and 11:00 and they all got entered in the correct format.
After
this first three it gets picky about what it will accept. It would no
longer
let me just enter 10 but owuld accept 1000, same with 15 and 1500 but it
would accept 11. Always seems to be after 3 times have been entered.
"Bob Phillips" wrote:
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
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
With Target
If .HasFormula = False Then
.Value = Replace(.Text, ":", "")
.NumberFormat = "General"
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
--
__________________________________
HTH
Bob
"Pyrite" wrote in message
...
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.