Excel Stopwatch

[cdb0ewm]

I have the follow set of macros that are a stopwatch with start, stop and reset buttons. The macro works perfect until you type in any cell of the spreadsheet at which time the cloc stops. Does anyone have a suggestion on how to keep the watch going even if an entry is made in the spreadsheet.

Thanks

Public StopIt1 As Boolean
Public ResetIt1 As Boolean
Public LastTime1

Private Sub CommandButton1_Click()
Dim StartTime1, FinishTime1, TotalTime1, PauseTime1
StopIt1 = False
ResetIt1 = False
If Range("e2") = 0 Then
StartTime1 = Timer
PauseTime1 = 0
LastTime1 = 0
Else
StartTime1 = 0
PauseTime1 = Timer
End If
StartIt1:
DoEvents
If StopIt1 = True Then
LastTime1 = TotalTime1
Exit Sub
Else
FinishTime1 = Timer
TotalTime1 = FinishTime1 - StartTime1 + LastTime1 - PauseTime1
TTime1 = TotalTime1 * 100
HM1 = TTime1 Mod 100
TTime1 = TTime1 \ 100
hh1 = TTime1 \ 3600
TTime1 = TTime1 Mod 3600
MM1 = TTime1 \ 60
SS1 = TTime1 Mod 60
Range("e2").Value = Format(hh1, "00") & ":" & Format(MM1, "00") & ":" & Format(SS1, "00")
If ResetIt1 = True Then
Range("e2") = Format(0, "00") & ":" & Format(0, "00") & ":" & Format(0, "00") & "." & Format(0, "00")
LastTime1 = 0
PauseTime1 = 0
End
End If
GoTo StartIt1
End If
End Sub
Private Sub CommandButton2_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
StopIt1 = True
End Sub
Private Sub CommandButton3_Click()
Range("e2").Value = Format(0, "00") & ":" & Format(0, "00") & ":" & Format(0, "00")
LastTime1 = 0
ResetIt1 = True
End Sub

 

[Jeffrey Mahoney]

Hey, I made a simple example of how to use Ontime.

I made two buttons; start and stop. I used three cells to run the timer. I gave them defined names: StartTime, NowTime, and TimeCounter. I formatted them all as h:mm:ss;@. I also defined a range called TimerRange that was all the 3 timer cells. The TimeCounter cell simply subtracts NowTime from StartTime.


These two subs are in the SHEET level code for sheet1

Private Sub Start_btn_Click()

  Sheets("Sheet1").Range("StartTime").Value = Now()
  Sheets("Sheet1").Range("NowTime").Formula = "=now()"
  TimerStatus = True
  Application.OnTime earliestTime:=Time + TimeValue("00:00:02"), Procedure:="UpdateTimer", schedule:=True
  
End Sub


Private Sub Stop_btn_Click()


  Sheets("Sheet1").Range("NowTime").Value = Sheets("Sheet1").Range("NowTime").Value
  TimerStatus = False


End Sub

This code I put in a standard MODULE

Public TimerStatus As Boolean

Sub UpdateTimer()
  Dim R As Range
  Set R = Sheets("Sheet1").Range("TimerRange")
  R.Calculate
  If TimerStatus = True Then
    Application.OnTime earliestTime:=Time + TimeValue("00:00:02"), Procedure:="UpdateTimer", schedule:=True
  End If
  
End Sub

OnTime runs once unless you call itself again. The TimerStatus is used to determine whether to call UpdateTimer again.

This will keep going until you press the Stop Button. If you are entering something into a cell (Editing) the timer will run once you finish with the cell.

Jeff

 

[Jeffrey Mahoney]

You used the term Stopwatch. If you were intending for it to be a countdown, you could add a cell that specifies the amount of time you want to stop at. Then add code in the UpdateTimer sub to determine if the time has elapsed.

Jeff

 

[cdb0ewm]

Got it thank you