Limit timer-running macro to one workbook, regardless of how many workbooks are open

[MsDeth]

Have a simple time recorder with 3 buttons that start, pause and reset the clock.
It works quite well EXCEPT that if I open/start working on another Excel file, the clock starts on the new file.
The "Time Elapsed" number pauses when I switch to another file, but continues working. When I return to that file the number updates.
The shown files are off by a second because of my timing, in reality they run together.
I've tried everything I can think of-including adding the workbook & worksheet names to the VBA.
What am I missing? (Much thanks in advance.)

Here is the basic code, based on one written by Philip Treacy, My Online Training Hub
'Timer or Stopwatch in Excel VBA

Option Explicit
Sub StartTimer2()

Dim Start As Single, RunTime As Single, CurrentlyElapsed As Single
Dim ElapsedTime As String

Range("F2").Value = 0
Range("D2").Interior.Color = 5296274 'Green

Start = Timer ' Set start time.
Do While Range("F2").Value = 0

DoEvents ' Yield to other processes.
RunTime = Timer ' current elapsed time
CurrentlyElapsed = RunTime - Start + Range("G2").Value
ElapsedTime = Format(CurrentlyElapsed / 86400, "hh:mm:ss")
Range("D2").Value = ElapsedTime
Application.StatusBar = ElapsedTime
Loop
Range("D2").Value = ElapsedTime
Range("D2").Interior.Color = 65535 'yellow
Range("G2").Value = CurrentlyElapsed
Application.StatusBar = False
End With
End Sub
--------------------
Sub StopTimer2()

'Set the control cell to 1
Range("F2").Value = 1
End Sub
--------------------
Sub ResetTimer2()

If Range("F2").Value > 0 Then
'Set the control cell to 1
Range("D2").Interior.Color = 16119285 'smoke
Range("D2").Value = Format(0, "hh:mm:ss")
Range("G2").Value = 0
End If
End Sub

 

[Jaafar Tribak]

I think you will need to fully qualify the ranges like this:

ThisWorkbook.Sheets("Sheet1").Range("D2").Value = ElapsedTime

Same for ranges F2 and G2.... Also, change Sheet1 to the actual name of the sheet where the ranges being updated are located.

 

[MsDeth]

Thank you for your suggestion.
Unfortunately if I do that, the timer stops completely if I go to another workbook. If I pause it, it will continue where it left off when I hit "start" again, but I cannot keep the timer running while working on another spreadsheet.

 

[Jaafar Tribak]

Thank you for your suggestion.
Unfortunately if I do that, the timer stops completely if I go to another workbook. If I pause it, it will continue where it left off when I hit "start" again, but I cannot keep the timer running while working on another spreadsheet.

In theory, working on other worksbooks shouldn't stop the timer.
Can you upload a workbook demo here so I can see what is happening ?

 

[MsDeth]

TimerStopwatchtest.xlsm
	A	B	C	D	E	F	G
1				Time Elapsed	Task name
2				0:00:00	test	1	0
3
4
5	START# starts timer
6	PAUSE# pauses timer
7	after pause, START# restarts timer
8	after pause, RESET# stops and resets timer
testsheet

 

[MsDeth]

Unfortunately I can't get the buttons to appear in the uploaded version. Is there a trick to that?

 

[Jaafar Tribak]

Unfortunately I can't get the buttons to appear in the uploaded version. Is there a trick to that?

You can use some file sharing website such as Secure File Sharing, Storage, and Collaboration | Box
After a free registration, you can upload the workbook there and then post a link here.