call macro based on cell value vba

[rohan85]

hi I am trying to call a macro of running clock based on cell value could you plz assist as iam unable to run the
Private Sub Worksheet Change(ByVal Target As Range) when I debug it does not move after
Private Sub Worksheet Change(ByVal Target As Range) many thanks plz find the below code

Private Sub Worksheet_Change(ByVal Target As Range)

lrow = ActiveSheet.Range("A121").End(xlUp).Offset(1, 0).Row

 If Not Intersect(Target, ActiveSheet.Range("D" & lrow)) Is Nothing Then
 If Target.Value = "Complete" Then Application.Run "runClock"
End If
End Sub


global clockOn As Boolean

Sub runClock()
 Range("A1").Value = Now()
If clockOn = True Then
 Application.OnTime Now + TimeValue("00:00:01"), "runClock"

End If
End Sub

Sub startClock ()
 clockOn=True
runClock
End Sub

Sub stopClock()
clockOn=False
End Sub

 

[ImranMalek]

Hi,
Not sure what you want to achieve but the following code will work fine.

Public clockOn As Boolean
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False


lrow = ActiveSheet.Range("A121").End(xlUp).Offset(1, 0).Row


 If Not Intersect(Target, ActiveSheet.Range("D" & lrow)) Is Nothing Then
 If Target.Value = "Complete" Then Call runClock
End If


Application.EnableEvents = True


End Sub




Sub runClock()
 Range("A1").Value = Now()
If clockOn = True Then
 Application.OnTime Now + TimeValue("00:00:01"), "runClock"


End If
End Sub


Sub startClock()
 clockOn = True
runClock
End Sub


Sub stopClock()
clockOn = False
End Sub

Hope it helps!

 

[rohan85]

hi thanks for yur help. I googled and found the below code. this is pasted in sheet1

it works till start time but when it comes to end iam not able to call the macro

Dim SchedRecalc As Date
 
Sub Recalc()
Dim lrow As Long

lrow = ActiveSheet.Range("a121").End(xlUp).Row


With Sheets("Data").Range("f" & lrow)
.Value = Format(Time, "hh:mm:ss AM/PM")
End With
Call SetTime
 
End Sub
 
Sub SetTime()
 
SchedRecalc = Now + TimeValue("00:00:10") 
Application.OnTime SchedRecalc, "Recalc"
 
End Sub
 

Sub Disable()
 
On Error Resume Next
 
Application.OnTime EarliestTime:=SchedRecalc, Procedure:="Recalc", Schedule:=False
 
End Sub

further iam trying to call the macros from a module using the below code but unable to call the macro disable it does not work

Private Sub Worksheet_Change(ByVal Target As Range)

Dim lrow As Long

lrow = ActiveSheet.Range("A121").End(xlUp).Row
       
    If Range("e" & lrow) = "O" Then
   Call SetTime
    
    
    If Range("e" & lrow) = "C" Then
   Call Disable
   
   

    End If
    End If
End Sub

 

[rohan85]

hi, thanks so much for yur previous code it works fine I have used your code thanks a lot