i have following script which is checking cell A and then accepting input in cell I and then filling cell L for current date and N for current time. then again taking input in cell T and filling cell U with current date (if there is some value in cell A)
then i have another script which is starting the clock:
and finally below script is to be used to stop the clock:
ok. all i need to merge all these 3 macro in 1. so, whenever i put some value in cell M and clock should start in cell W and when i put value in cell V and it should stop the clock and calculate and give me time in same cell ( W ) showing the total time since clock was running. (time when value was entered in cell V - time when value was entered in cell M = time)
pls help. thanks
Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim irow As Long
If Not Intersect(Target, Range("I:I")) Is Nothing Then
On Error Resume Next
Application.EnableEvents = False
irow = Target.Row
'if there is a date in column L end the macro
If IsEmpty(Target) Or IsEmpty(Target.Offset(0, -8)) Then
Target.Offset(0, 3).ClearContents
Target.Offset(0, 5).ClearContents
GoTo EndProc
End If
If IsDate(Target.Offset(0, 3)) Then GoTo EndProc
If Not IsEmpty(Target.Offset(0, -8)) Then
Target.Offset(0, 3) = Date
Target.Offset(0, 5) = Time
Else: GoTo EndProc
End If
End If
'---------------------------------
If Not Intersect(Target, Range("T:T")) Is Nothing Then
On Error Resume Next
Application.EnableEvents = False
irow = Target.Row
'if there is a date in column L end the macro
If IsEmpty(Target) Then
Target.Offset(0, 1).ClearContents
GoTo EndProc
End If
If IsDate(Target.Offset(0, 3)) Then GoTo EndProc
If Not IsEmpty(Target.Offset(0, -19)) Then
Target.Offset(0, 1) = Date
Else: GoTo EndProc
End If
End If
EndProc:
Application.EnableEvents = True
End Sub
then i have another script which is starting the clock:
Code:
Dim SchedRecalc As Date
Sub Recalc()
Range("C4").Value = Format(Time, "hh:mm:ss AM/PM")
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
Call SetTime
End Sub
<o:p></o:p>
Sub SetTime()
SchedRecalc = Now + TimeValue("00:00:01")
Application.OnTime SchedRecalc, "Recalc"
End Sub
and finally below script is to be used to stop the clock:
Code:
Sub Disable()
On Error Resume Next
Application.OnTime EarliestTime:=SchedRecalc, Procedure:="Recalc", Schedule:=False
End Sub
ok. all i need to merge all these 3 macro in 1. so, whenever i put some value in cell M and clock should start in cell W and when i put value in cell V and it should stop the clock and calculate and give me time in same cell ( W ) showing the total time since clock was running. (time when value was entered in cell V - time when value was entered in cell M = time)
pls help. thanks