Time Format

[Pyrite]

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.

 

[Bob Phillips]

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

 

[Pyrite]

Bob,

Thank you!! This works with or without : which is excellent!!

Now then, this has raised a new problem (as solutions generally do) because
the time is being forced to show in the format hh:mm:ss AM/PM. I have limited
space on the timesheet and every column pixel width is precious. Would it be
possible to now extend this to cut the time down to hh:mm?

I have tried some things myself but all I do is seem to stop the thing
working all together.

Thanks again Bob, I cannot tell you how much I appreciate that solution and
how much my eyes thank you for not having to repeatedly see 'Please Enter A
Valid Time' anymore.

 

[Pyrite]

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]

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

 

[Pyrite]

Bob,

Again, I cannot thank you enough. This is it, this is the soultion!! It
works everytime and is in the correct format.

I am sooooo happy to finally click yes at the bottom of this page.

You have brought joy and light to an amateur Excel users life (thats the
point of desperation it had got to) )

My thanks also to everyone else who helped me with this problem. I think the
amount of time people have offered toward this end shows an amazing community
spirit. I recommend this forum to everyone who comes to me with a problem
that I cannot solve and am happy to do so.

 

[Bob Phillips]

I am glad we have solved it, but be careful with such absolute statements,
there may be that little curly somewhere that throws it <bg>!

BTW, I think you access this via the Microsoft web forum. I actually connect
directly to the public newsgroups, that the MS forum gets its feed from. So
whilst I see your question, I am not on that site, or in that community, I
am in another.