Excel Stopwatch for Multiple Clients

[cdb0ewm]

Each day I get a list of clients that I have to track the time I spend doing various activity. The complicating part of this is that I will start working on one client, have to pause and work on another client, then potentially go back to the first client or other clients. I may have to hit the Start and Pause buttons several times during the day for a single customer. I envision a spreadsheet like:

Client Start Button Pause Button Stop Button Time Spent
Client A Button Button Button Time
Client B Button Button Button Time
Client C
ETC

I would appreciate any suggestions.

 

[tonyyy]

One approach...

Excel Workbook
	A	B	C	D	E	F	G	H
1	TotalTime	Client	Start01	Stop01	Interval01	Start02	Stop02	Interval02
2	0:00:37	abc company	14:01:59	14:02:12	0:00:13	14:02:16	14:02:40	0:00:24
Sheet1

To enter the Start and Stop times, just one button that calls the following macro...

Sub InsertTime()
    ActiveCell.Value = Format(Time, "h:mm:ss")
End Sub

Cheers,

tonyyy

 

[Worf]

Another approach…

- Each client would have a corresponding cell, linked to three buttons; start, stop and reset.
- Example below is for one client; when working with multiple clients and buttons, additional code can be used to identify which button was clicked and take proper action.

Cdb, please choose a method.

' sheet module
Dim StopTmr As Boolean, SchdTime As Date, Etime As Date
Const OneSec As Date = 1 / 86400#


Private Sub ResetBtn_Click()
StopTmr = 1: Etime = 0
[B3] = "00:00:00"               ' cell with timer
End Sub


Private Sub StartBtn_Click()
StopTmr = 0
SchdTime = Now()
[B3] = Format(Etime, "hh:mm:ss")
Application.OnTime SchdTime + OneSec, "Plan1.NxtTck"
End Sub


Private Sub StopBtn_Click()
StopTmr = True: Beep
End Sub


Sub NxtTck()
If StopTmr Then
   'Don't reschedule
Else
   [B3] = Format(Etime, "hh:mm:ss")
   SchdTime = SchdTime + OneSec
   Application.OnTime SchdTime, "Plan1.NxtTck"
   Etime = Etime + OneSec
End If
End Sub

 

[Worf]

This is the code to determine which button was clicked:

Sub FButton()                                           ' associated to the button
Dim b As Object
Set b = ActiveSheet.Buttons(Application.Caller)         ' Form control
With b.TopLeftCell
    MsgBox "Column " & .Column & vbLf & "Row " & .Row
End With
End Sub

 

[Worf]

- Tag: Excel multiple timers with VBA

- The code below manages three simultaneous stopwatches, each one independent with start, stop and reset buttons.

' sheet module
Dim StopTmr(1 To 3) As Boolean, SchdTime(1 To 3) As Date, Etime(1 To 3) As Date
Const OneSec As Date = 1 / 86400#
    
Sub NxtTck(i%)
If StopTmr(i) Then
   'Don't reschedule
Else
   Cells(5 * i - 3, 2) = Format(Etime(i), "hh:mm:ss")    ' cell with timer
   SchdTime(i) = SchdTime(i) + OneSec
   Application.OnTime SchdTime(i), "'Plan2.NxtTck """ & i & """'"
   Etime(i) = Etime(i) + OneSec
End If
End Sub


Sub FButton()                                           ' associated to each button
Dim b As Object, j%
Set b = ActiveSheet.Buttons(Application.Caller)         ' Form controls
Select Case b.TopLeftCell.Row
    Case 2: j = 1                                       ' row 2, first timer
    Case 7: j = 2                                       ' row 7, second timer
    Case 12: j = 3                                      ' row 12, third timer
End Select
Select Case b.TopLeftCell.Column
    Case 5                                              ' start buttons, column E
        StopTmr(j) = False
        SchdTime(j) = Now
        Cells(5 * j - 3, 2) = Format(Etime(j), "hh:mm:ss")
        Application.OnTime SchdTime(j) + OneSec, "'Plan2.NxtTck """ & j & """'"
    Case 9                                              ' stop buttons, column I
        StopTmr(j) = True
    Case 13                                             ' reset buttons, column M
        StopTmr(j) = True
        Etime(j) = 0
        Cells(5 * j - 3, 2) = "00:00:00"
End Select
End Sub

 

[cdb0ewm]

I think I've got it

Thank you vcery much