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!
Fingers crossed
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