VBA Time - Keep Running At Background

[Pratap 1987]

Hi Masters,

I have the below VBA to track the cycle time on the active cell

Sub Timer_click()
ay = ActiveCell.Row
With Worksheets("Sheet1").Cells(ay, 12)
.Font.Bold = Not .Font.Bold
End With
Update_Timer
End Sub

Sub Update_Timer()
ay = ActiveCell.Row
With Worksheets("Sheet1")
If .Cells(ay, 3) = Empty Or ay = 3 Or .Cells(ay, 12).Font.Bold Then Exit Sub
.Cells(ay, 12) = .Cells(ay, 12) + TimeValue("00:00:01")
End With
Timer_time
End Sub

Sub Timer_time()
Application.OnTime (Now + TimeValue("00:00:01")), "Update_Timer"
End Sub

Please refer attached image - this is what I am trying to achieve (based on the above VBA code).

Basically it is a query tracker and I track how much did I actually spend on the query.
Wherein, Col C has the Date & Time (when the query is raised) - And I need to track the cycle time in Col N.

So basically, when a new query is opened - I click on the respective cell in Col N and start the cycle time.
The time keeps updating every second - until I click the button again to pause or stop it.

Then I move on the next query and start tracking the cycle time into the respective cell.
Note: Query time tracking is not sequential - it is random (like I spend 10 mins on Query 1 - Pause it and then spend 15 mins of Query 2 - Pause it and then come back to Query 1 and resume the timing).

There is no issue with the above process - However, when I switch to another workbook or worksheet - the cycle timer stops.
I suspect it is because the VBA coding is based on active cell.

Is there a way to keep the cycle time running at the background - even If I work on other workbook or worksheets.

Sorry for trouble - I am just a beginner on VBA and struggling

With Kind Thanks,
Pratap

 

[Dermot]

Try this, changes in blue.
It should keep referring to the original workbook

Dim ay, WB

Sub Timer_click()
ay = ActiveCell.Row
Set WB = ActiveWorkbook
With Worksheets("Sheet1").Cells(ay, 12)
.Font.Bold = Not .Font.Bold
End With
Update_Timer
End Sub

Sub Update_Timer()
ay = ActiveCell.Row
With WB.Worksheets("Sheet1")
If .Cells(ay, 3) = Empty Or ay = 3 Or .Cells(ay, 12).Font.Bold Then Exit Sub
.Cells(ay, 12) = .Cells(ay, 12) + TimeValue("00:00:01")
End With
Timer_time
End Sub

Sub Timer_time()
Application.OnTime (Now + TimeValue("00:00:01")), "Update_Timer"
End Sub

 

[Pratap 1987]

Wow.
Thank you Dermot - Works like a PRO.

Regards,
Pratap