Run my timer macro in a workbook while working in others

[jacktc16]

Hi. Below is code I have that runs a start/stop timer in excel. I want to be able to select the start button, and continue working in other workbooks while the timer keeps running, and come back and click stop at a later time. The issue right now is that when I click start, then proceed to open other workbooks and try to enter anything in the cells, the timer stops when I go to edit a cell. I think my code is confusing excel on knowing where to put the times. Is there anything I can do to fix this issue?

[FONT=Calibri]Public StopIt As Boolean[/FONT]
[FONT=Calibri]Public ResetIt As Boolean[/FONT]
[FONT=Calibri]Public LastTime[/FONT]
[FONT=Calibri]Sub starttimerfunction()[/FONT]
[FONT=Calibri]Windows("Jack.xlsm").Activate[/FONT]
[FONT=Calibri]Dim r As Range[/FONT]
[FONT=Calibri]Dim StartTime, FinishTime, TotalTime, PauseTime[/FONT]
[FONT=Calibri]StopIt = False[/FONT]
[FONT=Calibri]ResetIt = False[/FONT]
[FONT=Calibri]Set r = ThisWorkbook.Sheets("Dog").<wbr>Buttons(Application.Caller).<wbr>TopLeftCell[/FONT]
[FONT=Calibri]If r = 0 Then[/FONT]
[FONT=Calibri]StartTime = Timer[/FONT]
[FONT=Calibri]  PauseTime = 0[/FONT]
[FONT=Calibri]  LastTime = 0[/FONT]
[FONT=Calibri]Else[/FONT]
[FONT=Calibri]  StartTime = 0[/FONT]
[FONT=Calibri]  PauseTime = Timer[/FONT]
[FONT=Calibri]End If[/FONT]
[FONT=Calibri]StartIt:[/FONT]
[FONT=Calibri]  DoEvents[/FONT]
[FONT=Calibri]  If StopIt = True Then[/FONT]
[FONT=Calibri]    LastTime = TotalTime[/FONT]
[FONT=Calibri]    Exit Sub[/FONT]
[FONT=Calibri]  Else[/FONT]
[FONT=Calibri]    FinishTime = Timer[/FONT]
[FONT=Calibri]    TotalTime = FinishTime - StartTime + LastTime - PauseTime[/FONT]
[FONT=Calibri]    TTime = TotalTime * 100[/FONT]
[FONT=Calibri]    HM = TTime Mod 100[/FONT]
[FONT=Calibri]    TTime = TTime \ 100[/FONT]
[FONT=Calibri]    hh = TTime \ 3600[/FONT]
[FONT=Calibri]    TTime = TTime Mod 3600[/FONT]
[FONT=Calibri]    MM = TTime \ 60[/FONT]
[FONT=Calibri]    SS = TTime Mod 60[/FONT]
[FONT=Calibri]    ThisWorkbook.Sheets("Dog").<wbr>Cells(r.Row, 4).Value = Format(hh, "00") & ":" & Format(MM, "00") & ":" & Format(SS, "00") & "." & Format(HM, "00")[/FONT]
[FONT=Calibri]    If ResetIt = True Then[/FONT]
[FONT=Calibri]      r = Format(0, "00") & ":" & Format(0, "00") & ":" & Format(0, "00") & "." & Format(0, "00")[/FONT]
[FONT=Calibri]      LastTime = 0[/FONT]
[FONT=Calibri]      PauseTime = 0[/FONT]
[FONT=Calibri]      End[/FONT]
[FONT=Calibri]    End If[/FONT]
[FONT=Calibri]    GoTo StartIt[/FONT]
[FONT=Calibri]  End If[/FONT]
[FONT=Calibri]End Sub[/FONT]
[FONT=Calibri]Sub stoptimerfunction()[/FONT]
[FONT=Calibri]StopIt = True[/FONT]
[FONT=Calibri]End Sub

[/FONT]

 

[JLGWhiz]

I am not a techie but I am wondering if vba can even run as a background program, since it compiles on the fly. I see the DoEvents in your code which would let other applications execute if any were in cue, but does it allow another program in the same instance of Excel application to execute? Your problem could be technical as opposed to code.

 

[Jaafar Tribak]

I don't think you can do that even with a windows timer because when excel enters Edit mode all code stops executing.

Can you run the code in a seperate instance of excel .. The two instances can inter-communicate via code if required.