Time format via Worksheet Change

[Pyrite]

Hi,

With some help yesterday 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. I was given the following code to
correct this but I just cannot get it to work/find the correct place for it.
The code I have for the time format is:


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


The code I have been given to add is:

Target.Value = CStr (Replace(Target.Value, ":", ""))


Where should this go and does anything need to be moved within the code? I
put it before the Application.EnableEvents = False and got a loop.

 

[paul.robinson]

Hi
Put in your line before the select case and call it TimeStr (see code
below). Now your select case has a string in the right format to work
on.
Not tested.

Application.EnableEvents = False
With Target
TimeStr= CStr (Replace(.Value, ":", ""))
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

regards
Paul

 

[Pyrite]

Paul,

Thank you very much for your rapid repsonse.

However, after making the change I am still having the same problem. If I
enter the time as 09:00 for instance the time is enterd into the cell in the
correct format but the error message appears, after clicking ok the time is
left in the cell in the correct format. I could leave it as is but as you no
doubt know I will get a million and one phone calls when the timesheet goes
to the end users. The strange thing is I can enter the time as 09.00 and it
just changes it to 09:00 no complaining. I dont understand how it can say the
time is incorrect when it is in the exact format it is changing it to.

 

[paul.robinson]

Hi
Try this 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
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

I was mixing up TimeStr and Target.Value in the code. Seems to work
now.

regards
Paul

 

[Pyrite]

Hmm,

I have copied this code from scratch to avoid missing any of the .Value to
TimeStr changes but it is still giving the same result when a time is entered
with the : in place. You have not entered a valid time

Thanks again for all your help.

 

[Pyrite]

I have just figured a little something out. The latest code you have offered
works, but only because of the ' in front of the On Error statement. This
stops the error coming up when entering a : with the time but if totally
erroneous dat ais entered it then brings up the standard VBA debug dialogue
box.