Timer

[sspatriots]

Good morning,

I am using this timer I found online to track time spent on different jobs task throughout the day. Seems to start and stop ok during the day. I minimized the screen over night and came in this morning and opened it up and the timer was still running (which is what I was testing out). However, as soon as I selected the stop button, the time went from 15 hours back to 8 hours. If I select the "Start" button again it will resume from the 8 hour number that it when back to. The "Reset" button is set up to copy the total time when ready to clear the field back to all zeroes and allow the user to put that value in a cell in a column that tallies the total time on the job. Also, I'm attaching a screenshot of an error message that another user got this morning when he closed the file and reopened it. Just looking to clean this up to avoid the errors. Seems to work fine as long as the screen is open during the day and stays open. Any help on this would be much appreciated.


Thanks, SS

Option Explicit

' Written by Philip Treacy, My Online Training Hub
' https://www.myonlinetraininghub.com/timer-stopwatch-excel-vba

Sub StartTimer()

    Dim Start As Single, RunTime As Single, CurrentlyElapsed As Single
    Dim ElapsedTime As String
    
    'Set the control cell to 0 and make it green
    Range("B1").Value = 0
    Range("C4").Interior.Color = 13561798 'Light Green
    Range("C4").Font.Color = 24832 'Dark Green

    'If Range("D1").Value <> 0 Then CurrentlyElapsed = Range("D1").Value
    
    Start = Timer    ' Set start time.
    Debug.Print Start
    Do While Range("B1").Value = 0
        
        DoEvents    ' Yield to other processes.
        RunTime = Timer    ' current elapsed time
        CurrentlyElapsed = RunTime - Start + Range("D1").Value
        ElapsedTime = Format(CurrentlyElapsed / 86400, "hh:mm:ss")
'        ElapsedTime = Format(CurrentlyElapsed / 86400, "hh:mm")
        'Display currently elapsed time in C4
        Range("C4").Value = ElapsedTime
        Application.StatusBar = ElapsedTime
            
    Loop
        
    Range("C4").Value = ElapsedTime
    Range("C4").Interior.Color = 13551615 'light Red
    Range("C4").Font.Color = 393372 'Dark Red
    Range("D1").Value = CurrentlyElapsed
    Application.StatusBar = False

End Sub

Sub StopTimer()

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

Sub ResetTimer()

Dim myRange As Range
Dim CopyRange As Range

Set CopyRange = Range("C4")

On Error Resume Next
Set myRange = Application.InputBox(Prompt:="Select Cell you want to capture your total time in.", Title:="Format Titles", Type:=8)

If myRange Is Nothing Then
    MsgBox "No selection made", vbCritical, "Input required"
    Exit Sub
End If

myRange.Value = CopyRange.Value

    If Range("B1").Value > 0 Then
       
        'Set the control cell to 1
        Range("C4").Value = Format(0, "hh:mm:ss")
'        Range("C4").Value = Format(0, "hh:mm")
        Range("C4").Interior.Color = 10284031 'light Gold
        Range("C4").Font.Color = 22428 'Dark Brown
        Range("D1").Value = 0
    
    End If
    
End Sub

[ATTACH type="full"]97192[/ATTACH][ATTACH type="full"]97193[/ATTACH]

 

[offthelip]

try this, which I think does do what you wnt:

Option Explicit

' Written by Philip Treacy, My Online Training Hub
' https://www.myonlinetraininghub.com/timer-stopwatch-excel-vba
Public Start As Variant
Sub StartTimer()

    Dim Start As Variant, RunTime As Variant, CurrentlyElapsed As Variant
    Dim ElapsedTime As String
    
    'Set the control cell to 0 and make it green
    Range("B1").Value = 0
    Range("C4").Interior.Color = 13561798 'Light Green
    Range("C4").Font.Color = 24832 'Dark Green

    'If Range("D1").Value <> 0 Then CurrentlyElapsed = Range("D1").Value
    
    Start = Time - Range("C4").Value  ' Set start time subtract existing count
    Debug.Print Start
    Do While Range("B1").Value = 0
        
        DoEvents    ' Yield to other processes.
 '       RunTime = Timer    ' current elapsed time
 '       CurrentlyElapsed = RunTime - Start + Range("D1").Value
        CurrentlyElapsed = Time - Start
'        ElapsedTime = Format(CurrentlyElapsed / 86400, "hh:mm:ss")
         ElapsedTime = Format(CurrentlyElapsed, "hh:mm:ss")
'        ElapsedTime = Format(CurrentlyElapsed / 86400, "hh:mm")
        'Display currently elapsed time in C4
        Range("C4").Value = ElapsedTime
        Application.StatusBar = ElapsedTime
            
    Loop
        
    Range("C4").Value = ElapsedTime
    Range("C4").Interior.Color = 13551615 'light Red
    Range("C4").Font.Color = 393372 'Dark Red
    Range("D1").Value = CurrentlyElapsed
    Application.StatusBar = False

End Sub

 

[sspatriots]

@sspatriots When you click the stop button, are you saying that the button should actually be a 'Pause' button? In other words if you wait for 5 minutes after clicking the 'Stop' button, & then select the 'Start' button, should the time being displayed start at the 5 minutes later or should it start displaying the time right after the the 'Stop' button was initially pushed?

Yes, the "STOP" button is also a "PAUSE". The whole idea is to be able to pause the timer while our PM"s here are being interrupted with other business. Then select "START" again to start the clock again from where it left off. SS

 

[sspatriots]

try this, which I think does do what you wnt:

Option Explicit

' Written by Philip Treacy, My Online Training Hub
' https://www.myonlinetraininghub.com/timer-stopwatch-excel-vba
Public Start As Variant
Sub StartTimer()

    Dim Start As Variant, RunTime As Variant, CurrentlyElapsed As Variant
    Dim ElapsedTime As String
  
    'Set the control cell to 0 and make it green
    Range("B1").Value = 0
    Range("C4").Interior.Color = 13561798 'Light Green
    Range("C4").Font.Color = 24832 'Dark Green

    'If Range("D1").Value <> 0 Then CurrentlyElapsed = Range("D1").Value
  
    Start = Time - Range("C4").Value  ' Set start time subtract existing count
    Debug.Print Start
    Do While Range("B1").Value = 0
      
        DoEvents    ' Yield to other processes.
 '       RunTime = Timer    ' current elapsed time
 '       CurrentlyElapsed = RunTime - Start + Range("D1").Value
        CurrentlyElapsed = Time - Start
'        ElapsedTime = Format(CurrentlyElapsed / 86400, "hh:mm:ss")
         ElapsedTime = Format(CurrentlyElapsed, "hh:mm:ss")
'        ElapsedTime = Format(CurrentlyElapsed / 86400, "hh:mm")
        'Display currently elapsed time in C4
        Range("C4").Value = ElapsedTime
        Application.StatusBar = ElapsedTime
          
    Loop
      
    Range("C4").Value = ElapsedTime
    Range("C4").Interior.Color = 13551615 'light Red
    Range("C4").Font.Color = 393372 'Dark Red
    Range("D1").Value = CurrentlyElapsed
    Application.StatusBar = False

End Sub

Thank you, that seems to address the issue with the timer not going back to all zeroes when the "Start" button is selected. However, will it fix the issue of the macro stopping a little while after you minimize the workbook and do other things on the PC? I saw in comment #9 the suggestion to use a variable to store start time an end time. I found the code below that I had referenced in my comment #8, but not sure if this is what is being referred to or not or even how to make this work if I do need something like this.

Private Sub Workbook_Open()
    TimerSet
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    StopTimer
End Sub

Public nextTime As Double

Sub TimerSet()
    nextTime = Now + TimeValue("00:00:30")
    Application.OnTime nextTime, "TimerSet"
    Calculate
End Sub

Sub StopTimer()
    Application.OnTime nextTime, "TimerSet", , False
End Sub

 

[offthelip]

I tried minimising it and it seems to work Ok, Becuase it works out the displayed time from the the actual start time ( minus whatever was already in the counter) it should still work even if you press stop at the end of the day, save the workbook shut the machine down and then the next day open it up and start the timer again, it will carry on adding to the original value as stored in C4. The start time is stored as a PUBLIC variable it will remain available all the time the workbook is open. i.e it should remain when minimised. It certainly would be a good idea to add the code to stop the timer before closing the workbook . whether you want to restart the timer automaticaly when you open the workbook really depends on how you want it to work. I would think it might be OK if you check the value in C4 and if it not zero then start it. if you always start it means it start even if the user hasn't selected it to start. A design issue not coding really

 

[sspatriots]

I tried minimising it and it seems to work Ok, Becuase it works out the displayed time from the the actual start time ( minus whatever was already in the counter) it should still work even if you press stop at the end of the day, save the workbook shut the machine down and then the next day open it up and start the timer again, it will carry on adding to the original value as stored in C4. The start time is stored as a PUBLIC variable it will remain available all the time the workbook is open. i.e it should remain when minimised. It certainly would be a good idea to add the code to stop the timer before closing the workbook . whether you want to restart the timer automaticaly when you open the workbook really depends on how you want it to work. I would think it might be OK if you check the value in C4 and if it not zero then start it. if you always start it means it start even if the user hasn't selected it to start. A design issue not coding really

Thanks again for all your help. I went ahead and came up with the following code events to make sure the clock never stops even though I minimize or close the workbook altogether. I'm sure there is an easier way, but this is working for now.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Range("F1").Value = Now()

    StopTimer

End Sub

Private Sub Workbook_Open()

Dim CurrentlyElapsed As Variant, ElapsedTime As Variant
  
    CurrentlyElapsed = Now() - Range("F1").Value

    ElapsedTime = CurrentlyElapsed + Range("C4").Value

    Range("C4").Value = ElapsedTime

    StartTimer

End Sub


Private Sub Workbook_WindowResize(ByVal Wn As Window)

Dim CurrentlyElapsed As Variant, ElapsedTime As Variant

    If Wn.WindowState = xlMaximized Then
    
        CurrentlyElapsed = Now() - Range("F1").Value
    
        ElapsedTime = CurrentlyElapsed + Range("C4").Value
    
        Range("C4").Value = ElapsedTime
    
        StartTimer
    
    ElseIf Wn.WindowState = xlMinimized Then
    
        Range("F1").Value = Now()
    
        StopTimer
    
    End If

End Sub