Log in  \/ 
x
or
x
x
Register  \/ 
x

or

How to create stopwatch in Excel worksheet?

If there is a stopwatch in your worksheet, you will know the time to finish a job. How can you create a stopwatch in a sheet with a Start, Stop, and Reset Button as following screenshot shown? If you click the Start button, it will start time, if you click Stop button, it will stop time, and the Reset button can help you to reset time. This article, I will talk about how to create a simple and handy stopwatch in Excel.

doc create stopwatch 1

Create a stopwatch with three buttons by using VBA code


Create a stopwatch with three buttons by using VBA code

To create a stopwatch with three buttons, Start, Stop and Reset, please do with the following steps one by one.

Firstly, insert three command buttons.

1. Click Developer > Insert > Command Button, see screenshot:

doc create stopwatch 2

2. Then drag the mouse to draw a button, after inserting the button, you can change the button caption, please click Developer > Properties, in the Properties dialog, enter the new caption “Start” for this button in the textbox beside the Caption, see screenshots:

doc create stopwatch 3  2 doc create stopwatch 4

3. Repeat the above two steps to insert other two buttons and caption them as “Stop” and “Reset”, see screenshot:

doc create stopwatch 5

4. After inserting the buttons, you should exit the design mode by clicking Developer > Design Mode.

Secondly, create a VBA code.

5. And then, right click the current worksheet tab, and choose View Code, in the popped out Microsoft Visual Basic for Applications window, please copy and paste the following code into the Module:

VBA code: Create stopwatch:

Public StopIt As Boolean
Public ResetIt As Boolean
Public LastTime
Private Sub CommandButton1_Click()
Dim StartTime, FinishTime, TotalTime, PauseTime
StopIt = False
ResetIt = False
If Range("C2") = 0 Then
  StartTime = Timer
  PauseTime = 0
  LastTime = 0
Else
  StartTime = 0
  PauseTime = Timer
End If
StartIt:
  DoEvents
  If StopIt = True Then
    LastTime = TotalTime
    Exit Sub
  Else
    FinishTime = Timer
    TotalTime = FinishTime - StartTime + LastTime - PauseTime
    TTime = TotalTime * 100
    HM = TTime Mod 100
    TTime = TTime \ 100
    hh = TTime \ 3600
    TTime = TTime Mod 3600
    MM = TTime \ 60
    SS = TTime Mod 60
    Range("C2").Value = Format(hh, "00") & ":" & Format(MM, "00") & ":" & Format(SS, "00") & "." & Format(HM, "00")
    If ResetIt = True Then
      Range("C2") = Format(0, "00") & ":" & Format(0, "00") & ":" & Format(0, "00") & "." & Format(0, "00")
      LastTime = 0
      PauseTime = 0
      End
    End If
    GoTo StartIt
  End If
End Sub
Private Sub CommandButton2_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
  StopIt = True
End Sub
Private Sub CommandButton3_Click()
  Range("C2").Value = Format(0, "00") & ":" & Format(0, "00") & ":" & Format(0, "00") & "." & Format(0, "00")
  LastTime = 0
  ResetIt = True
End Sub

doc create stopwatch 6

Note: In the above code, C2 is the cell where the stopwatch time will be inserted, and the CommandButton1, CommandButton2, CommandButton3 are the button names, you can view the exact button name from the Name Box, see screenshot:

doc create stopwatch 7

Thirdly, format the stopwatch time cell.

6. Then you should format the time cell as Text format, and you can change the cell size, font, font color, background, etc. as you need, see screenshot:

doc create stopwatch 8

7. After finishing above steps, from now on, when you click Start button, the time will be start now, and click Stop button, it will stop time, the Reset button will reset the time, see screenshot:

doc create stopwatch 9


Demo: Create a stopwatch with three buttons by using VBA code


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.