Pause stopwatch

D

Damian37

Active Member
Joined
Jun 9, 2014
Messages
301
Office Version
  1. 365
Hello All,
I've created a stopwatch macro to create a start time and a stop time as well as a Duration section, however, the people that will be using this stopwatch often pause the stopwatch to go to lunch, or a break. My start and stop macros are functioning properly, however, when I click on the pause button the stopwatch stops instead of pausing. I was hoping someone could help me in creating a pause macro that will pause the stopwatch and then allow the start button to resume the time. This way the total amount of time will be captured. I've included the code I'm currently using as well as my sample file so everyone can see how the macros are currently working. Here is the code:

VBA Code: 
Public interval As Date
Sub Start_Timer()
    Range("F1:F10000").Find("").Select
    ActiveCell.Value = Time
    Selection.NumberFormat = "hh:mm:ss"
End Sub

Sub Pause_Timer()
On Error Resume Next
    Call Stop_Timer
        Application.OnTime EarliestTime:=interval, Procedure:="Start_Timer", Schedule:=False
End Sub
Sub Stop_Timer()
    Range("G1:G10000").Find("").Select
    ActiveCell.Value = Time
    Selection.NumberFormat = "hh:mm:ss"
    
    Range("H1:H10000").Find("").Select
    ActiveCell.Value = Format(Time, "Long Time")
    ActiveCell.FormulaR1C1 = "=RC[-1] - RC[-2]"
    Selection.NumberFormat = "hh:mm:ss"
End Sub

Here is the link to the sample file: Stopwatch Macro


I appreciate any help anyone can provide. Thank you.

D.
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Sort by date Sort by votes
I tried adding this code snippet for pausing, but it doesn't seem to be pausing it.

VBA Code: 
Sub Pause_Timer()
    On Error Resume Next
    If btnPause.Caption = "Pause" Then
        Paused = True
        btnPause.Caption = "Continue"
    Else
        Paused = False
        btnPause.Caption = "Pause"
    End If
End Sub

Thanks,
D.
 
Upvote 0
While you're waiting for a solution, please have a look at this page and see if there's anything you can use.
 
Upvote 0
kevin9999 said:
While you're waiting for a solution, please have a look at this page and see if there's anything you can use.
Click to expand...
I've seen a few of those code snippets. I also found this code, but I'm not sure how I can show the start time and the stop time within the excel file. Also, showing the duration taken for each case. This is the code I've found.

VBA Code: 
Option Explicit
Dim CmdStop As Boolean
Dim Paused As Boolean
Private Sub btnStart_Click()
    CmdStop = False
    Paused = False
    Dim TimerValue As Date
    Dim pausedTime As Date
    Start = Now()    ' Set start time.
    btnPause.Enabled = True
    btnStop.Enabled = True
    btnReset.Enabled = False
    Do While CmdStop = False
        If Not Paused Then
            TimerValue = Now() - Start - pausedTime
        Else
            pausedTime = Now() - TimerValue - Start
        End If
        TimerReadOut.Caption = Format(TimerValue, "h:mm:ss")
        DoEvents    ' Yield to other processes.
    Loop
End Sub
Private Sub btnPause_Click()
    If btnPause.Caption = "Pause" Then
        Paused = True
        btnPause.Caption = "Continue"
    Else
        Paused = False
        btnPause.Caption = "Pause"
    End If
 
End Sub
Private Sub BtnReset_Click()
    TimerReadOut.Caption = "0:00:00"
    btnStop.Enabled = False
End Sub
Sub BtnStop_Click()
    btnPause.Enabled = False
    btnReset.Enabled = True
    btnStop.Enabled = False
    CmdStop = True
End Sub

Can you see where I can add code that would show the start time, stop time, and the duration like I have in the current code that I'm using. Just a reminder of that code:
Code: 
Public interval As Date
Sub Start_Timer()
    Range("F1:F10000").Find("").Select
    ActiveCell.Value = Time
    Selection.NumberFormat = "hh:mm:ss"
End Sub

Sub Pause_Timer()
    On Error Resume Next
    If btnPause.Caption = "Pause" Then
        Paused = True
        btnPause.Caption = "Continue"
    Else
        Paused = False
        btnPause.Caption = "Pause"
    End If
End Sub

Sub Stop_Timer()
    Range("G1:G10000").Find("").Select
    ActiveCell.Value = Time
    Selection.NumberFormat = "hh:mm:ss"
    
    Range("H1:H10000").Find("").Select
    ActiveCell.Value = Format(Time, "Long Time")
    ActiveCell.FormulaR1C1 = "=RC[-1] - RC[-2]"
    Selection.NumberFormat = "hh:mm:ss"
End Sub

Thanks,
D.
 
Upvote 0
Hello All,
I've tried to adjust my code some to incorporate a pause button. However, the pause button doesn't appear to be working correctly. I was hoping someone might be able to take a look at my new code and let me know what I'm doing incorrectly. I would very much appreciate it. Thank you. Code below:

VBA Code: 
Public interval As Date
Sub Start_Timer()
    Range("F1:F10000").Find("").Select
    ActiveCell.Value = Time
    Selection.NumberFormat = "hh:mm:ss"
End Sub

Sub Pause_Timer()
On Error Resume Next
    Application.Wait Now
End Sub

Sub Resume_Timer()
On Error Resume Next
    Application.Resume Now
Call Start_Timer
End Sub

Sub Stop_Timer()
    Range("G1:G10000").Find("").Select
    ActiveCell.Value = Time
    Selection.NumberFormat = "hh:mm:ss"
    
    Range("H1:H10000").Find("").Select
    ActiveCell.Value = Format(Time, "Long Time")
    ActiveCell.FormulaR1C1 = "=RC[-1] - RC[-2]"
    Selection.NumberFormat = "hh:mm:ss"
End Sub

I've also uploaded a new spreadsheet for you to see the issue I'm dealing with. StopWatch Macro Ver 2 Thank you.

D.
 
Upvote 0
I can't see a way of producing what you want without the use of a number of helper columns, which you could hide, although I would recommend that you keep them visible. Here is my proposed code:

VBA Code: 
Sub Start_Timer_2()
    With Cells(Rows.Count, "H").End(xlUp)
        If .Offset(1, -2) = "" Or .Offset(1, 3) = "Y" Then
            .Offset(1, 1) = Time
            .Offset(1, 3) = ""
            If .Offset(1, -2) = "" Then .Offset(1, -2) = Time
        End If
    End With
End Sub

Sub Pause_Timer_2()
    With Cells(Rows.Count, "H").End(xlUp)
        If .Offset(1, -2) <> "" And .Offset(1, 3) <> "Y" Then
            .Offset(1, 2) = (Time - .Offset(1, 1)) + .Offset(1, 2)
            .Offset(1, 3) = "Y"
        End If
    End With
End Sub

Sub Stop_Timer_2()
    With Cells(Rows.Count, "G").End(xlUp)
        If .Offset(1, -1) <> "" Then
            .Offset(1) = Time
            .Offset(1, 1) = (Time - .Offset(1, 2)) + .Offset(1, 3)
            .Offset(1, 4) = ""
        End If
    End With
End Sub

Based on the following worksheet:
Stopwatch Macro version 2.xlsm
ABCDEFGHIJK
1NameCase IDDateLocationComments1st StartStopTotal DurationLatest StartCumul. DurationPaused?
2GenericGenericGenericGenericGeneric
3GenericGenericGenericGenericGeneric
4GenericGenericGenericGenericGeneric
Sheet1


From this file.
 
Upvote 0
kevin9999 said:
I can't see a way of producing what you want without the use of a number of helper columns, which you could hide, although I would recommend that you keep them visible. Here is my proposed code:

VBA Code: 
Sub Start_Timer_2()
    With Cells(Rows.Count, "H").End(xlUp)
        If .Offset(1, -2) = "" Or .Offset(1, 3) = "Y" Then
            .Offset(1, 1) = Time
            .Offset(1, 3) = ""
            If .Offset(1, -2) = "" Then .Offset(1, -2) = Time
        End If
    End With
End Sub

Sub Pause_Timer_2()
    With Cells(Rows.Count, "H").End(xlUp)
        If .Offset(1, -2) <> "" And .Offset(1, 3) <> "Y" Then
            .Offset(1, 2) = (Time - .Offset(1, 1)) + .Offset(1, 2)
            .Offset(1, 3) = "Y"
        End If
    End With
End Sub

Sub Stop_Timer_2()
    With Cells(Rows.Count, "G").End(xlUp)
        If .Offset(1, -1) <> "" Then
            .Offset(1) = Time
            .Offset(1, 1) = (Time - .Offset(1, 2)) + .Offset(1, 3)
            .Offset(1, 4) = ""
        End If
    End With
End Sub

Based on the following worksheet:
Stopwatch Macro version 2.xlsm
ABCDEFGHIJK
1NameCase IDDateLocationComments1st StartStopTotal DurationLatest StartCumul. DurationPaused?
2GenericGenericGenericGenericGeneric
3GenericGenericGenericGenericGeneric
4GenericGenericGenericGenericGeneric
Sheet1


From this file.
Click to expand...
Thanks Kev, I will try this code out. I don't mind the use of a few columns as long as I can get the clock to pause. I looked at the code quickly, is there a reset button? Thanks again for all of your help.

D.
 
Upvote 0
Damian37 said:
is there a reset button?
Click to expand...
Not sure what you mean by "reset button" (there was no reset button on the file you shared). What would you expect a 'reset' button to do? As it is, pressing 'stop' will 'finalise' the 'current' row, and further starts will commence on a new row. This will also apply if a row is in a state of 'pause'.
 
Upvote 0
kevin9999 said:
Not sure what you mean by "reset button" (there was no reset button on the file you shared). What would you expect a 'reset' button to do? As it is, pressing 'stop' will 'finalise' the 'current' row, and further starts will commence on a new row. This will also apply if a row is in a state of 'pause'.
Click to expand...
Hi Kev,
I was going over the reply I sent you, and I guess I was thinking in reference of a timer, and not a "stop watch" scenarion. A reset button would normally zero out the values making the code start at 1 second again, and track the new amount of time a reviewer would take to review a scenario. Sorry for the confusion. Thank you so much for your help. I will let you know the outcome once I run the code using the sample file they sent me. Cheers!

D.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
VBA button click from within a macro
Replies
2
Views
517
Chris_yorks
C
R
  • Question
Three-lane Stopwatch?
Replies
7
Views
169
RaymondW
R
S
  • Solved
Timer
2
Replies
14
Views
1K
sspatriots
S
S
  • Question
VBA Form - Need Button - Time - Start & pause
Replies
5
Views
750
dmt32
D
I
  • Solved
Using InStr to Find Matches Between Columns
Replies
1
Views
551
Farscape2020
F

Forum statistics

Threads
1,215,069
Messages
6,122,956
Members
449,096
Latest member
Anshu121

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top