Stopwatch for Excel-based timesheet

juldum

New Member
Joined
Jan 4, 2017
Messages
1
Hi!

I am attempting to create an Excel spreadsheet that will serve as a timesheet. The timesheet is structured simply: lines are activities, and columns are days (dd-mm-yyyy).

To make things as easy to use as possible, I'd like users of the timesheet to be able to double-click a cell to trigger a stopwatch inside that cell, which would display elapsed time in hh:mm. Similarly, they would be able to double-click a cell with an ongoing stopwatch inside it to stop the stopwatch. Double-clicking a cell with an elapsed time already inside it would resume the stopwatch using that elapsed time as a starting point.

Finally, it would be absolutely fantastic if users could override times by inputting values manually using the formula bar, and still be able to use the stopwatch functionnality after doing that, using the manually inputted time as the starting point.

The spreadsheet should allow only one stopwatch to run at any given moment.

I found code from VBA Express : Excel - Create a StopWatch in Excel that provides a starting point for this kind of functionnality, however my VBA skills are not good enough to fully adapt it to my need. I was hoping to find someone who could help me achieve this in exchange for immense gratitude!

Code:
Public stopMe As Boolean 
Public resetMe As Boolean 
Public myVal As Variant 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 
    If Target.Column = 1 Then 
        If Target.Value = myVal And Target.Value <> "" Then 
             'Changed
            Dim startTime, finishTime, totalTime, timeRow 
            startTime = Timer 
            stopMe = False 
            resetMe = False 
            myTime = Target.Offset(, 2).Value 
            Target.Offset(, 1).Select 
startMe: 
            DoEvents 
            timeRow = Target.Row 
            finishTime = Timer 
            totalTime = finishTime - startTime 
            Target.Offset(, 1).Value = Format(myTime + totalTime, "0.0000") & " Seconds" 
            If resetMe = True Then 
                Target.Offset(, 1).Value = 0 
                Target.Offset(, 2).Value = 0 
                stopMe = True 
            End If 
            If Not stopMe = True Then 
                Target.Offset(, 2).Value = totalTime 
                GoTo startMe 
            End If 
            Cancel = True 
            End 
        Else 
             'Not Changed
            stopMe = True 
            Cancel = True 
        End If 
    End If 
End Sub 
Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
    myVal = Target.Value 
End Sub

Fingers crossed :rolleyes:
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Watch MrExcel Video

Forum statistics

Threads
1,122,719
Messages
5,597,736
Members
414,170
Latest member
Mdm

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
Top