Time Format on Worksheet Change

[Pyrite]

Hi,

With some help today (thanks a lot Paul Robinson) I have used some code on a
Worksheet Change to
ensure that time is being entered into my spreadsheet in the correct format.
So far it all works brilliantly and all time is displayed as hh:mm no matter
what is entered whether it be a full 1030 or just 9 etc. The only problem I
have is that if the user chooses to put the : in themselves (which some will)
it returns a 'Enter A Valid Time' error. Paul helped add the Replace
statement to try and take the : out of play but it still isnt working. Could
anyone else offer any suggestions?

Basically if you enter 09:30 you get the error message but then 09:30
remains in the cell in the correct format. I could leave it like this but
when it goes to the end users I will get hundreds of phone calls because they
are getting an error even though the end result is still correct. I could
just use On Error Resume Next but I think this would be a little lazy.

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
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, 2) & ":00"
Case 2 ' e.g., 12 = 12:00 AM
TimeStr = TimeStr & ":00"
Case 3 ' e.g., 123 = 1:23 AM
TimeStr = Left(TimeStr, 1) & ":" & _
Right(TimeStr, 2)
Case 4 ' e.g., 1234 = 12:34 AM
TimeStr = Left(TimeStr, 2) & ":" & _
Right(TimeStr, 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

 

[Mike H]

Hi,

the line
TimeStr = CStr(Replace(TimeStr, ":", ""))
won't remove the colon which is what the other poster suggested so
try this. Not extensively tested so It may not be bulletproof but seems to
work. Note the extra 'Exit Sub' 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
'***

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


Mike

 

[Pyrite]

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.

 

[Mike H]

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]

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

 

[Pyrite]

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.