Time with VBA code

[Dale G]

I’m having some trouble with this time VBA code.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim TimeStr As String
On Error GoTo EndMacr

If Application.Intersect(Target,
Range("D3200")) Is Nothing Then
Exit
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 = "h: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

First is there a way to use this code in two columns without using the
entire range like A1:E200
Let’s say A1:A200 & E1:E200?
Also, I use the code to enter time in column E (Actual time) & column D has
a fixed time & column F has a (= the difference set up). All of this is done
in military time. The problem is; when the fixed time in column D is 23:55 &
I enter the actual time of 24:10 it does not work. The difference column will
show 5 minutes late, & the actual time will show 0:00. Any solution?
Lastly is it possible to use this code as a module so I would not have to
have the entire code attached to each sheet?

Any help is appreciated.

 

[Joel]

To use more than one column use and AND statement here

If Application.Intersect(Target, Range("A1:A200")) Is Nothing and _
Application.Intersect(Target, Range("E1:E200")) Is Nothing then
Exit
end if


To put data in another column use the following

Range("E" & Target.Row) = TimeStr

 

[Dale G]

Thank you. The first part works well.
~If Application.Intersect(Target, Range("A1:A200")) Is Nothing and _
Application.Intersect(Target, Range("E1:E200")) Is Nothing then
Exit
end if~

I'm not sure where to put the second part. ~Range("E" & Target.Row) = TimeStr~

Would you know anything about my other dilemas?

How can i display 12:01 midnight (00:01) ?

 

[Joel]

Add line here

End With
Range("E" & Target.Row) = TimeStr

Application.EnableEvents = True
Exit Sub


I don't know exactly what your input data looks like so it is hard to give a
good answers. I guessing seeing your code and assuming you know what you are
doing how to answer your questions.. It is nice to see examples of you input
data to give the best answers.

since you have a valid time string "TimeStr" which looks like : 1:23. to
convert this into serial time ( a microsoft number indicating time) do this

STime = TimeValue(TimeStr)

Now to format the time use the format statement

NewTimeStr = format(STime,"hh:mm")

 

[Dale G]

Thank you.
Actually, I don't know much of what I'm doing, so your help is appreciated.
I could send the workbook if that would be helpful.
One problem I’m having is the hour of 0:00-01:00 or 24:00-01:00.
Here is my layout of column A-F

Location Route Time Actual Diff Vehicle
4th & Union 150 23:47 3232

I will enter the actual time a vehicle passes that location, and the diff
column will do the math.
When I enter 2400 in the actual column the code enters 0:00.
It will work if I enter the time with the colon manually.

 

[Dale G]

Correction; When I enter any time in the 2400 hour (like 2403) in the actual
column the code enters 0:00.

It will work if I enter the time with the colon manually. (like 24:03)

 

[Joel]

I don't know hwat you r present code look like. I took a guess based on the
response I made to the previous postings. I tried to make the code more
robust since I was getting errors depending on the data I put in. the code
was erroring if I entered a real time value so I fixed that problem. the
code was also failing if the cell was formated as time rather than general.
I fixed that problem also. I'm putting the results into column E.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim TimeStr As String
On Error GoTo EndMacro

If Application.Intersect(Target, Range("A1:A200")) Is Nothing And _
Application.Intersect(Target, Range("E1:E200")) Is Nothing Then

Exit Sub
End If

If Target.Cells.Count > 1 Then Exit Sub

If Target.Value = "" Then Exit Sub
If Not IsNumeric(Target.Value) Then Exit Sub

Application.EnableEvents = False

With Target
If .HasFormula = False Then
TimeStr = Target.Text
If InStr(TimeStr, ":") = 0 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
'if number is sgreater than 24 hours (2400)
'make less than 24 hours
TimeStr = Val(TimeStr) Mod 2400
TimeStr = Format(TimeStr, "#0000")
TimeStr = Left(TimeStr, 2) & ":" & _
Right(TimeStr, 2)

Case Else

Err.Raise 0
End Select


Else
TimeStr = TimeValue(TimeStr)
End If
STime = TimeValue(TimeStr)
NewTimeStr = Format(STime, "hh:mm")
Range("E" & Target.Row).NumberFormat = "@"
Range("E" & Target.Row) = NewTimeStr
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

 

[Dale G]

I'm not getting that code to work. It’s very close. Sometimes the time
doesn’t show on the first attempted, then I noticed when I enter it again it
will appear. (When I entered 610 or 701)
Also, the 2401 will show as 00:01 which is good but, the other columns that
are related & do math against the time I enter, are not working right. It’s
like they‘re not receiving the input.
I’m placing your code in column E3:E200 & I3:I200. (I changed the code
accordingly)
In the worksheet column C has a hard entered time (that is a set schedule)
and after I make the time entry in column E, column F is set as =E-C for the
difference. I also use column I to enter the time on a different day. Column
J is set to =I-C for the difference on that day.
I don’t know if that explanation is helpful. The code is close. What do you
think?

 

[Joel]

fiurst make sure you change these two statements if you are changing the
columns

Range("E" & Target.Row).NumberFormat = "@"
Range("E" & Target.Row) = NewTimeStr


the @ signb is changing the format of the cells to text. while I was
debuggijng the code yesterday I found that I had some problems when the
source cell was set to something other than Genal Format.

The code should alwas\ys give the same results when the same data is enter
into the same cell.

I suspect the problem may be that different cells are the worksheet are
format different. You can change the format of the whole shet by Typing
Cntl-A (select All) and then changing the format to General. I would run an
experiement and see if the format of the cell where you change the data make
the results differeent. Try formating to : general, Text, Number, and Time
and see if this gives different reults.