Timer code not active when sheet isn't active

[acecard]

Hi all,

As part of my super-spreadsheet, I have a timer that starts when the value in a cell changes. Piecing together various things that I have come across on the net, I have come up with the following macro coding:

Dim CountDown As Date
Dim count As Range
Sub RunTime()
CountDown = Now + TimeValue("00:00:01")
If count.Value >= TimeValue("00:00:01") Then
   Application.OnTime CountDown, "Counter"
Else
   Beep
   Call DisableCount
End If
End Sub
Sub Counter()
Set count = Range("b14")
count.Value = count.Value + TimeValue("00:00:01")
Call RunTime
End Sub
Sub DisableCount()
On Error Resume Next
Application.OnTime EarliestTime:=CountDown, Procedure:="Counter", Schedule:=False
End Sub
 
Sub Reset()
Set count = Range("b14")
On Error Resume Next
Application.OnTime EarliestTime:=CountDown, Procedure:="Counter", Schedule:=False
count.Value = TimeValue("00:00:00")
End Sub

And here is the worksheet change coding:

Sub worksheet_change(ByVal target As Range)
    If Not Intersect(target, target.Worksheet.Range("b13")) Is Nothing Then
 Call DisableCount
 Call Reset
 Call Counter
End If
End Sub

This seems to work fine, however, it flashes up an error if I switch sheets whilst it is running, and doesn't activate if cell b13 changes whilst I'm not on the sheet.

How does one make it such that the timer code will run irrespective of which sheet I am on? I'm guessing I have to specify the name of the sheet to start with, however I don't know how to implement this.

Also, how might I make it such that the timer stops once it reaches say 40 secs?

Many thanks!

 

[mole999]

timer code needs to live in a module, then it should be live all the time in the spreadsheet

 

[acecard]

Hi thanks for the reply. I have all of the timer macros already in a module (so not within the specific sheet objects), but it still behaves this way.

 

[mole999]

is your worksheet code in the worksheet

 

[acecard]

Yes, it's in the specific worksheet object

 

[acecard]

Just an update that I managed to solve the issue by defining the worksheet that the timer relates to using the "Dim ws As Worksheet" and Set ws = ThisWorkbook.Worksheets("worksheet name") functions.