Vba Stopwatch unintendently resets back 0 when changing other cells

Question

I have a VBA stopwatch in my Excel spreadsheet, code:

Public StopIt As Boolean
Public ResetIt As Boolean
Public LastTime
Private Sub CommandButton1_Click()
Dim StartTime, FinishTime, TotalTime, PauseTime
StopIt = False
ResetIt = False
If Range("C2") = 0 Then
  StartTime = Timer
  PauseTime = 0
  LastTime = 0
Else
  StartTime = 0
  PauseTime = Timer
End If
StartIt:
  DoEvents
  If StopIt = True Then
    LastTime = TotalTime
    Exit Sub
  Else
    FinishTime = Timer
    TotalTime = FinishTime - StartTime + LastTime - PauseTime
    TTime = TotalTime * 100
    HM = TTime Mod 100
    TTime = TTime \ 100
    hh = TTime \ 3600
    TTime = TTime Mod 3600
    MM = TTime \ 60
    SS = TTime Mod 60
    Range("C2").Value = Format(hh, "00") & ":" & Format(MM, "00") & ":" & Format(SS, "00") & "." & Format(HM, "00")
    If ResetIt = True Then
      Range("C2") = Format(0, "00") & ":" & Format(0, "00") & ":" & Format(0, "00") & "." & Format(0, "00")
      LastTime = 0
      PauseTime = 0
      End
    End If
    GoTo StartIt
  End If
End Sub
Private Sub CommandButton2_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
  StopIt = True
End Sub
Private Sub CommandButton3_Click()
  Range("C2").Value = Format(0, "00") & ":" & Format(0, "00") & ":" & Format(0, "00") & "." & Format(0, "00")
  LastTime = 0
  ResetIt = True
End Sub

This stopwatch works correctly. My problem is that when I change a cell in my spreadsheet or make any changes it resets the stopwatch to '0'.

I want it to run throughout my session as I have other cells which refer to this counter.

Any help would be greatly appreciated. I could not find any similar problems while searching for a solution to this.

Thanks

Answer 1

Not sure if you have found a solution yet, but I did some research and think I know why your code doesn't work, and possibly have another solution... if acceptable...

When I test your code by starting the timer then changing any cell, it doesn't reset to zero but it does stop the timer. Looking at the code you have (which maybe came from https://www.extendoffice.com/documents/excel/3684-excel-create-stopwatch.html), the code is only good for using a simple timer ... nothing else. And since it is never relinquishes control until you stop it, it uses a tremendous amount of your processor (take a look at Task Manager!)

I did find code here on Stack VBA Macro On Timer style to run code every set number of seconds, i.e. 120 seconds and used the second answer (simply too lazy to use first answer at startup).

You can now change cells and the code continues to run (except it 'pauses' while a change is being made to a cell). You may not like the fact that it increments by seconds, but maybe someone else knows a solution to that.

The code does NOT go in a sheet module.

Option Explicit

Dim TimerActive As Boolean
Sub StartTimer()
    Start_Timer
End Sub

Private Sub Start_Timer()
    TimerActive = True
    Application.OnTime Now() + TimeValue("00:00:01"), "Timer"
End Sub

Private Sub Stop_Timer()
    TimerActive = False
End Sub

Private Sub Timer()
    If TimerActive Then
        Activesheet.Cells(2, 3).value = Time
        Application.OnTime Now() + TimeValue("00:00:01"), "Timer"
    End If
End Sub