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