Microsoft Excel is one of the most useful applications available, and we can utilize a dataset unlimitedly using Excel’s features and tools. In this article, we will discuss how to construct a simple stopwatch. A stopwatch clock can assist us in keeping track of how much time is left to complete a task or how much time remains until a deadline. With this in mind, we will examine the step-by-step techniques of How to Create a Stopwatch in Excel.
What Is a Stopwatch?
A Stopwatch is a tool for tracking how long something takes to happen. This type of watch stands out as exceptional in terms of how precisely it can record the passage of time. We must use a timer to precisely record an event’s duration. Intuitively set the timers. To begin counting, push the start button. A timer will freeze time at the end of the event if the stop button is pressed.
Step-by-Step Procedures to Create Stopwatch in Excel
If we know the proper steps, it can be easy to use Excel to make a Stopwatch. This post will show you how to make a Stopwatch clock in Excel in 6 steps. In the first step, it will show the time. We’ll write VBA code throughout the following three steps to start, stop, and re-start the watch. In the next step, we’ll discuss how to make buttons and link them to macros in the Stopwatch Timer. Last but not least, we will test the Time-tracker in the previous step. Follow these steps carefully to figure out how to create a Stopwatch in Excel.
Step 1: Display Stopwatch Clock in Excel
In the first part of this procedure, you’ll learn how to make a simple clock that looks good. Please follow the steps below to set up the Timer.
- First of all, select the B4:C4Â range.
- Secondly, navigate to the Home tab.
- Thirdly, choose the Merge and Center option from the Alignment group, and Middle and Center align.
- After that, from the Font group, select the fill color as Blue Lighter 80% and pick the Bold and All Border.
- At this time, again, from the Home tab and click the Expand icon from the Number group.
- Subsequently, the Format Cells will appear.
- Later, select Custom and write h:mm:ss in the Type box.
- Then, hit OK.
- Next, pick the B4 cell and input 0:00:00.
- Finally, hit Enter, which will produce the desired Clock like the below one.
Read More: How to Add Command Button Programmatically with Excel VBAÂ
Step 2: Establish VBA Macro to Start Stopwatch
In the second step, we will write some VBA code to initiate the Stopwatch. Here, we will declare a public countdown variable, and consequently, we can access this variable via many procedures.
- First, go to the Developer tab and then click on Visual Basic.
- Second, click Insert and then Module to get a Module Box.
- Third, copy and paste the code below into the Module Box.
Option Explicit
Public countDown As Date
Sub StartTimer()
countDown = Now + TimeValue("00:00:01")
Range("B4") = Range("B4") + TimeValue("00:00:01")
Application.OnTime countDown, "StartTimer"
End Sub
- In this case, we gave the Procedure Name the name StartTimer. The Stopwatch App starts up when this Macro is run.
- Press the Ctrl + S key now.
Step 3: Generate Another Macro to Reset Time-Tracker
Currently, we must create another macro to reset the Clock so that we can utilize this Timer regularly. Initially, we will use this Macro to set the clock time to 0 seconds. Please follow the below directions to complete the assignment.
- Like before, open the same Module.
- After that, put the following code in the Module Box.
Sub ResetTimer()
Range("B4") = TimeValue("00:00:0")
End Sub
- In this part, we called ResetTimer the Procedure Name. This Macro can reset the Clock.
- Now, hit Ctrl + S .
Step 4: Build Last Macro to STOP Stopwatch in Excel
We will build a technique for stopping the Time-tracker in the following part. Please follow the directions below to complete the assignment.
- Similarly, open the Module. Module1 is what we call it in our study.
- Then, type the code below into the Module Box to make another Macro called StopTimer.
Sub StopTimer()
Application.OnTime EarliestTime:=countDown, Procedure:="StartTimer", Schedule:=False
End Sub
- We will use this Macro, in particular, to stop the Time-tracker App.
- Tap the Ctrl + S key next.
Step 5: Implement Buttons for Stopwatch
Finally, we will design buttons to increase the interactivity of our program. Here, we’ll create three buttons and assign previously-created macros to each.
- To start, visit the Developer tab.
- Second, from the Controls group, tap on the Insert icon.
- Then, a small bar will appear.
- Then, from the Form Controls, choose the Button symbol.
- Currently, make a rectangle right below the B4Â cell.
- As a result, the Assign Macro window will display.
- At this point, choose the StartTimer procedure, followed by OK.
- Now, click on the button’s text area and click on the Home tab.
- After that, choose Middle and Center align from the Alignment group.
- Afterward, click on the B icon and change the font size to 20.
- Last, change the name of the button. In this case, it’s START.
- Make another button to stop or pause, just like you did before.
- Now, draw this button and place it below Button1.
- Because of this, it will show the Assign Macro window.
- Next, click on the Macro called StopTimer, then click OK.
- Now, click on the text area of the button and then on the Home tab.
- Then, click the B icon and change the font size to 20.
- Afterward, in the Alignment group, choose Middle and Center align.
- Last, change what the button is called. It is indeed STOP in this case.
- Like before, make another button to reset.
- Now, draw this button and put it under the Button2.
- As a result, the Assign Macro window will appear.
- Then, click on the Macro called ResetTimer, and click OK.
- Now, click the button’s text area and then the Home tab.
- Then, click the B symbol and change the font size to 20.
- Then, in the Alignment section, choose Middle and Center align.
- Last, change the button’s name. In this situation, it is RESET.
Read More: How to Make a Calculate Button in Excel
Step 6: Check Stopwatch Timer
Now, we’ll put our Stopwatch App to the test. First, we’ll start over with the Time-tracker. After that, we can start it up and turn it off.
- Click the Reset button to start.
- Press the STARTÂ button.
- Tap the STOP button when you’re done.
- Then, set the Stopwatch Timer back to Zero.
- Press the START button now.
- Press STOP when you’re done.
- Tap the RESET button again right now.
- So, the Stopwatch Timer is working precisely as it should.
Download Practice Workbook
Please click on the link below this paragraph if you want a free copy of the sample workbook covered in the presentation.
Conclusion
You can Create A Stopwatch in Excel by following the steps below. Please provide any additional recommendations or enhanced techniques as you continue to use them. Remember to put your thoughts, questions, and instructions in the space provided.
Related Articles
- Text Alignment with VBA Command Button in Excel
- VBA Code for Save Button in ExcelÂ
- How to Use VBA Code for Submit Button in Excel
Hi, thanks and its useful. However, when i click on the stop button after i already stop the timer. i hit error. how can i solve this? please advise. thanks
Hello JAMIE
Your appreciation means a lot to us. Thanks for your nice words!
You often click the stop button after you have already stopped the timer. That results in an Error. Thank you once again for noticing the issue. Your claim is correct.
However, I am delighted to inform you that the issue can be avoided with the help of an Error Handler called On Error Resume Next.
All you need to do is to modify the StopTime sub-procedure to fulfil your requirement.
Modified Excel VBA Code:
Good luck!
Regards
Lutfor Rahman Shimanto
Thank you for this useful code.
How can I add a 2nd instance?
I simply attemted to copy and rename the macros and target another cell.
The 2nd instance sarts ans then stops immediately… The reset works.
Thanks for help.
Sub StartTimer2()
countDown = Now + TimeValue(“00:00:01”)
Range(“F4”) = Range(“F4”) + TimeValue(“00:00:01”)
Application.OnTime countDown, “StartTimer”
End Sub
Sub ResetTimer2()
Range(“F4”) = TimeValue(“00:00:0″)
End Sub
Sub StopTimer2()
On Error Resume Next
Application.OnTime EarliestTime:=countDown, Procedure:=”StartTimer”, Schedule:=False
End Sub
Hello BIC
Thanks for reaching out and posting an interesting problem. You want to create a 2nd instance of the existing stopwatch code.
Excel VBA Code (2nd Instance):
2nd Instance of stopwatch code
OUTPUT:
Hopefully, the idea will fulfil your requirements. Good luck!
Regards
Lutfor Rahman Shimanto
Hi,
Whilst the stop watch is running and I try to switch tabs to another sheet in the workbook it generates a Run-time error ’13’ type mismatch. How can I resolve this?
Thanks
Adam
Hello ADAM INGLETON
Thanks for reaching out and posting an exciting query. You are right about the run-time error “13” Type mismatch. This is because the active sheet contains text value within cell B4.
I am delighted to inform you that I have developed other improved sub-procedures that have overcome the issue you mentioned by modifying the previous code. To be specific, I have taken a worksheet object to avoid this error.
Raised Error on Your End:
Improved Sub-procedures:
OUTPUT of Applying the Improved Sub-procedures:
Hopefully, the idea will resolve your problem. Good luck!
Regards
Lutfor Rahman Shimanto
Hi Lutfor,
Thanks for you help with this much appreciated
Thanks,
Adam
Hello Adam Ingleton,
You are most welcome.
Regards
ExcelDemy
Good Afternoon,
Do you know if it is possible to make the clock work in metric time (100 minute clock?)
Thanks,
Adam
Dear ADAM,
Thanks for your comment. Unfortunately, Excel’s current time formatting does not support metric time display.
Regards
Aniruddah
Team Exceldemy
Hi, Is it possible to change the colour of the buttons?
Thanks
Adam
Dear Adam,
Thank you for your concern. You can not change the color of Excel button but you can use Command Button to change the button color.
You can follow the steps below to color your command button:
1. use Developer tab > Insert > ActiveX Controls > Command Button.
2. A command will appear and right click on it.
3. Choose CommandButton Object > Edit.
Name the button as Start.
4. Now right click on the command button and choose View Code.
5. Then write code below for starting.
Finally the button color has been added.
6. Now create a stop buttom and use the code below:
7. Write down the code for reset button:
Finally, you will have colored command button. You can change the color by changing color code 13959039.
How to make multiple stop watch on one sheet
Hello RAMI
Thanks for visiting our blog and sharing your queries. You want to create multiple stopwatches in a sheet. Of course, the requirement is very much achievable.
I have displayed four stopwatches in the B4, F4, B15, and F15 cells.
OUTPUT OVERVIEW:
Follow these steps:
I hope you have learned how to create multiple stopwatches in a sheet. I am also attaching the solution workbook for better understanding; good luck.
DOWNLOAD SOLUTION WORKBOOK
Regards
Lutfor Rahman Shimanto
Excel & VBA Developer
ExcelDemy