VBA Time - Keep Running At Background

Pratap 1987

Board Regular
Joined
Feb 28, 2011
Messages
95
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
 

Attachments

  • Timer Sheet.JPG
    Timer Sheet.JPG
    41.2 KB · Views: 14

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Dermot

Board Regular
Joined
Aug 11, 2006
Messages
85
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,128,074
Messages
5,628,484
Members
416,321
Latest member
tomazik123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top