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.
The code is all in one standard module and looks like this:
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.
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.
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
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
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
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
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
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
Hi Guys,
Upon surfing around for help with this I came across this code.
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
How about this:
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
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
By the way I am running Office 2003 Professional SP2 if that helps in anyway
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.
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
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
It might be helpful. http://www.xnotestopwatch.com Counts time externally and pastes split/lap directly to Excel.