Excel VBA Stopwatch breaking at midnight

Question

I have a worksheet which I use to track progress recording audiobooks (I'm a narrator). On each sheet, I have a timer that I use to track how many hours I record each day/session.

If the timer is running at midnight, it sets the timer to 20:00:00 and keeps going. I often am recording at midnight, so this is a major issue if I don't remember to stop the timer before midnight and restart it after.

Here is a link to the whole Excel file. You can find the timer in the TEMPLATE worksheet. The Macros for the timer are within Module1.

http://recording-tracker.narratedby.me

Here is all the code:

    Option Explicit

Dim NextTick As Date, t As Date, PreviousTimerValue As Date, strSheetName As String, lRowTime As Long, lRowDate As Date, CurrentTimerValue As Date, StartTimerValue As Date


Sub StartTime()
strSheetName = ActiveSheet.Name

StartTimerValue = PreviousTimerValue
CurrentTimerValue = PreviousTimerValue
t = Time
Call ExcelstrSheetName
End Sub

Private Sub ExcelstrSheetName()

strSheetName = ActiveSheet.Name

ThisWorkbook.Worksheets(strSheetName).Range("Q1").Value = Format(Time - t + PreviousTimerValue, "hh:mm:ss")

NextTick = Now + TimeValue("00:00:01")

Application.OnTime NextTick, "ExcelstrSheetName"

End Sub

Sub StopClock()

strSheetName = ActiveSheet.Name

On Error Resume Next

Application.OnTime earliesttime:=NextTick, procedure:="ExcelstrSheetName", schedule:=False

CurrentTimerValue = ThisWorkbook.Worksheets(strSheetName).Range("Q1").Value

End Sub

Sub Reset()

strSheetName = ActiveSheet.Name

CurrentTimerValue = ThisWorkbook.Worksheets(strSheetName).Range("Q1").Value
On Error Resume Next

Application.OnTime earliesttime:=NextTick, procedure:="ExcelstrSheetName", schedule:=False

ThisWorkbook.Worksheets(strSheetName).Range("Q1").Value = 0

End Sub

The cell Q1 is what holds the timer value at any given time.

I have removed other Subs and portions of these Subs which are irrelevant to my question. This is the basic stopwatch function.

Any help is much appreciated!

question from:https://stackoverflow.com/questions/65713825/excel-vba-stopwatch-breaking-at-midnight

Answer 1

Waitting for answers