Im 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("D3:D200")) 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
Lets 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.

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" wrote:

Im 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("D3:D200")) 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
Lets 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.

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" wrote:

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" wrote:

Im 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("D3:D200")) 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
Lets 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.

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" wrote:

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" wrote:

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" wrote:

Im 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("D3:D200")) 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
Lets 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.

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 Im 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.


"Joel" wrote:

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" wrote:

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" wrote:

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" wrote:

Im 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("D3:D200")) 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
Lets 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.

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)




"Dale G" wrote:

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 Im 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.


"Joel" wrote:

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" wrote:

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" wrote:

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" wrote:

Im 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("D3:D200")) 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
Lets 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.