Stopwatch

John asks how one macro can be used to stop another macro. One way to do it is with a variable that both procedures can see. In this implementation, I have two buttons from the Forms toolbar that start and stop a counter in A1.

two buttons on an excel sheet and a number in A1

The code is all in one standard module and looks like this:

Private bStopped As Boolean
 
Sub StartIt()
 
    bStopped = False
    Sheet1.Shapes(“Button 1”).Locked = True
   
    Do While Not bStopped
        DoEvents
        With Sheet1.Range(“A1”)
            .Value = .Value + 1
        End With
    Loop
   
    Sheet1.Shapes(“Button 1”).Locked = False
   
End Sub
 
Sub StopIt()
 
    bStopped = True
   
End Sub

StartIt runs until the variable is made true by StopIt. The DoEvents in the loop releases control of the computer to any other processes in the queue. In this case, running StopIt will queue it up but won’t run unless you let it. It’s kind of like letting people in to your lane – DoEvents is that jerk in front of you that’s letting everyone in.

I set the Locked property of the Start button, but I’m not sure if I know what I’m doing. Without those lines pressing Start repeatedly crashed Excel without fail. With those lines, no problems.

The key is to make your variable available to both the start and stop procedures. Since both procedures in this example are in a standard module, my variable can be private to that module. It’s the smallest scope that both procedures can see. If my procedures were in a userform, a variable that was private to the userform’s class module would do the trick. If the procedures are in different modules, you’ll need to declare the variable as Public in a standard module.

Posted in Uncategorized

15 thoughts on “Stopwatch

  1. Hi –

    Came across this posting, and I’m new to programming macros.

    I’m trying to come up with something to make my job easier. I’m a script supervisor for live TV shows, and I like to have a macro in my excel that works as a stop watch. I’d like to be able to set a specific start time… start the clock…. and be able to grab times and place them in different parts of my excel document to make time calculations. Is this possible?

    You can email me for more specifics. I have no idea how to program this.

    Thanks
    Melissa W.

  2. Melissa

    This is definitely possible, but not a beginner project. I am a Tv director and Excel MVP, so I guess I’m supposed to be able to do this. Email the specifics to

    harald dot staff at nrk dot no

    and I’ll have a look at it. Specify what the start time is for, how the stopwatch should behave (start, stop, reset, set, lap time, …), and what and why “differents parts of my excel document” is.

    Best wishes Harald

  3. I am looking for 1 button to start a counter.
    And one button to stop the counter.
    A cell that will add all of the start and stops from the two buttons listed above.
    The cell will accumulate all start stops until it is cleared out !! And ideas ?
    Thanks
    RHB
    robert.bracher@rauland.com

  4. I need a button to stop a macro do you have code for it i just need to stop the macro from running when it is clicked

  5. To whom,
    I am making a spreadsheet in excel and would like to enter a start date & time in one cell, end date & time in another cell, and a third cell to add the total hours between dates & time. I can do it as long as it is under 24 hours, but not over. Can anyone out there give me some advice?

    Thank you,
    Jim

  6. Hi,

    Can you please send me the same advice that you sent to Robert on August 8th, 2005 at 11:21 am.

    I am looking for 1 button to start a counter.
    And one button to stop the counter.
    A cell that will add all of the start and stops from the two buttons listed above.
    The cell will accumulate all start stops until it is cleared out !! And ideas ?

    Many thanks, Steve

  7. Hi Guys,
    I am trying to cut out some work I have to do regularly and I came across this site. I too am trying to create a stopwatch function in Excel. I have to test user login times at remote sites a couple times a month. The problem is that I have to write out everything and then put it into Excel when I get back. What I would like to be able to do is click (or double click) a “Start Time” cell and have the exact time entered into the cell. Once they are logged in I just click the adjacent cell and the same thing is applied. It would be nice if the elapsed time was automagically inserted into the next cell but I live without it. I am new to Excel functions and VBA but I am trying to figure this out. I am more than sure it’s possible. Thank any of you in advance for any help you can offer.

    Please email me at wufather at gmail dot com if you can offer help.

    Thanks again,
    Patrick

    Example of what I need
    =========================================================

    Username | Start Time | Stop Time | Elapsed Time (sec)
    ============================================================
    John 08:03:13 08:03:43 40
    Jane 08:07:10 08:03:22 12

  8. Hi Guys,
    Upon surfing around for help with this I came across this code.

    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    If Target.Column = 8 Then
        ActiveCell.Formula = Now()
       
    End If

    If Target.Column = 9 Then
        ActiveCell.Formula = Now()
    End If

    End Sub

    This does exactly what I was looking for…. almost. It is very sensitive and if I accidentally click on the cell again it updates. What I would like to do now is make it either

    1)Enter/Update time only upon a double-click.
    2)Lock the cell after data is entered unless the Delete key is used.

    Still trying to figure out how to make it automagically put the elapsed time next to the other cells. Thank you all in advance again, for any help you can offer.

    Patrick

  9. How about this:

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
       
        If Target.Column = 2 Or Target.Column = 3 Then
            Cancel = True
            Target.Value = Now
            If Target.Column = 3 Then
                Target.Offset(0, 1).Formula = “=” & Target.Address(0, 0) & _
                    “-“ & Target.Offset(0, -1).Address(0, 0)
            End If
        End If
       
    End Sub
  10. Hi Dick,
    Thanks for the code. I dropped it into my worksheet (replacing the other code). I get syntax errors when I run it. The top Private sub line goes yellow and the target.offset lines go red. I am new to VB editing so I have no clue how to distinguish what the problem is. However, this little project has sparked my interest enough that I am going to try and learn all I can now. Thank you for your help by the way. Please let me know if there is anything else I can do to fix this.

    I tried it with the original code above it and still got the same syntax errors.

    I also got the “elapsed time” column to work by doing ” =I2-H2 ” and formatting the column to display ” :ss “. That works great and doesn’t change if you accidentally click on the cell again. I’m off to figure out this double click problem. Hope to hear from you on here today if you get a chance. Thanks again.

    Patrick

  11. Patrick: Re Target.Offset – the code rendering is screwed up in the comments on this blog. Every time it says ‘& amp ;’ (remove spaces and quotes), you need to replace that with an ampersand (Shift+7). The BeforeDoubleClick line is probably yellow because the target.offset line is a compile error. Once you fix that, it should fix both.

  12. Dick,
    I left a comment but don’t see it showing up. I hope this isn’t redundant but you are the freaking man! It works perfectly. I’m going to analyze the code and see if I can figure out how to do something else now. We have to go do this at multiple sites on different days. What I’m going to try to do is make the previous cell do almost the same thing with the date that its done. I’ll let you know how it goes. It will be a good learning experience if I can make it work.

    Patrick

  13. Dick,
    Easy fix.. I just added an extra Target.Column entry in your code and formatted the column to display dates. Works like a champ! Thanks again.

    Patrick


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published. Required fields are marked *