Three-lane Stopwatch?

RaymondW · Mar 27, 2024

I'm trying to create a three (or more) -lane stopwatch in Excel and I'm not getting expected results. A single Start button launches a macro that loops through a series of checks to see if any of the lane stop buttons have been clicked and update the running time in three cells. If a lane stop button is clicked, the time for that lane should stop incrementing but the other two will continue to update. I have some other things I want to do with this like resume a lane after a stop (not including the stopped time) and continuing a lane after a stop but still accumulating the time during the stop. For now, though, I'd just like to get this much working.

The start button works to start the displays for all three lanes. Clicking start again while the subroutine is running, restarts all time displays from zero.

The reset button stops all lane displays but only resets the values to zero after a second click. It does not end the subroutine which continues to run until I hit Run ~ Reset in the vb editor. Occasionally, if I het reset while the subroutine is running, the three displays jump to a completely unexpected time value. (Start, 2 seconds later, Reset, all displays show 16:38)

The three stop buttons stop all three lanes together regardless of which stop button is clicked.

I'll need to add an exit button, too, to end the subroutine without clearing the accumulated values.

Can someone please show me the error of my ways or is this just not the way to go about this? Thanks for any help.

This is my code so far. It includes snippets I've found in various articles and examples:

VBA Code:

'Option Explicit
Public Stop1 As Boolean
Public Stop2 As Boolean
Public Stop3 As Boolean
Public ResetAll As Boolean
Dim Lane1 As Boolean
Dim Lane2 As Boolean
Dim Lane3 As Boolean

Private Sub StartButton_Click()

Stop1 = False                                     ' all stop flags are off
Stop2 = False
Stop3 = False
ResetAll = False

Lane1 = True                                      ' all lanes are active
Lane2 = True
Lane3 = True

StartTime = Timer                                 ' start time is set
StartTime1 = StartTime
StartTime2 = StartTime
StartTime3 = StartTime

Do
  DoEvents
  NowTime = Timer
  If Stop1 = True Then
    StopTime1 = NowTime
    TotalTime1 = StopTime1 - StartTime1
    DisplayTime1 = TotalTime1
    Lane1 = False
  ElseIf Stop2 = True Then
    StopTime2 = NowTime
    TotalTime2 = StopTime2 - StartTime2
    DisplayTime2 = TotalTime2
    Lane2 = False
  ElseIf Stop3 = True Then
    StopTime3 = NowTime
    TotalTime3 = StopTime3 - StartTime3
    DisplayTime1 = TotalTime3
    Lane3 = False
  Else
    If Lane1 = True Then DisplayTime1 = NowTime - StartTime1      ' change the displayed time only if the lane is still active
    If Lane2 = True Then DisplayTime2 = NowTime - StartTime2
    If Lane3 = True Then DisplayTime3 = NowTime - StartTime3
    
    TTime = DisplayTime1 * 100                                    ' update the displayed time (stopped or active) for each lane
    HM = TTime Mod 100
    TTime = TTime \ 100
    hh = TTime \ 3600
    TTime = TTime Mod 3600
    MM = TTime \ 60
    SS = TTime Mod 60
    Range("G2").Value = Format(hh, "00") & ":" & Format(MM, "00") & ":" & Format(SS, "00") & "." & Format(HM, "00")
    
    TTime = DisplayTime2 * 100
    HM = TTime Mod 100
    TTime = TTime \ 100
    hh = TTime \ 3600
    TTime = TTime Mod 3600
    MM = TTime \ 60
    SS = TTime Mod 60
    Range("G3").Value = Format(hh, "00") & ":" & Format(MM, "00") & ":" & Format(SS, "00") & "." & Format(HM, "00")

    TTime = DisplayTime3 * 100
    HM = TTime Mod 100
    TTime = TTime \ 100
    hh = TTime \ 3600
    TTime = TTime Mod 3600
    MM = TTime \ 60
    SS = TTime Mod 60
    Range("G4").Value = Format(hh, "00") & ":" & Format(MM, "00") & ":" & Format(SS, "00") & "." & Format(HM, "00")

    If ResetAll = True Then Exit Sub
  End If
Loop
End Sub
Private Sub StopButton1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
  Stop1 = True
End Sub
Private Sub StopButton2_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
  Stop2 = True
End Sub
Private Sub StopButton3_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
  Stop3 = True
End Sub
Private Sub ResetButton_Click()
  Range("G2").Value = Format(0, "00") & ":" & Format(0, "00") & ":" & Format(0, "00") & "." & Format(0, "00")
  Range("G3").Value = Format(0, "00") & ":" & Format(0, "00") & ":" & Format(0, "00") & "." & Format(0, "00")
  Range("G4").Value = Format(0, "00") & ":" & Format(0, "00") & ":" & Format(0, "00") & "." & Format(0, "00")
  ResetAll = True
End Sub

Micron · Apr 6, 2024

I could try to help if I had a file, but am not up to creating all of that. My intent would be to try to solve as much of it as possible. If you want to post a link to a file share I will probably download it as long as I don't have to be a member there.

RaymondW · Apr 6, 2024

Thank you for your offer to help. The link to the file on Dropbox is:

Stopwatch.xlsm

They encourage you to sign in or sign up but you don't have to. You can continue to download only. If there's any problem, please let me know and I will find another host site.

The Start button in the workbook runs Macro1 and starts all three stopwatches.

Thank you again.

Micron · Apr 6, 2024

So what you want to be able to do per cell/button:
- pause display and timer, or
- pause display but continue timer

What if you want all 3 to stop at the same time? It doesn't look like you can (without having to reset).

You want both of these results, or are you saying either is a problem?

The start button works to start the displays for all three lanes. Clicking start again while the subroutine is running, restarts all time displays from zero.

The weird reset results seem to be caused by everything after this being executed when it probably shouldn't

VBA Code:

  Else
    If Lane1 = True Then DisplayTime1 = NowTime - StartTime1      ' change the displayed time only if the lane is still active
    If Lane2 = True Then DisplayTime2 = NowTime - StartTime2
    If Lane3 = True Then DisplayTime3 = NowTime - StartTime3
etc.

What I might do, assuming I understand the goal:
Click a main button:
- if caption is "Start" all 3 start (assuming that must always be the case). Then Start caption changes to "Pause/Hold".
- if caption is Pause/Hold, pause all clocks, change caption to "Resume". Timer holds its value.
- if caption is "Resume" resume all 3 clocks from where they were. Change caption to Pause/Hold

A Reset button stops all clocks, sets clocks to zero, sets timer to zero
For each individual clock button:
- the captions for your current Stop buttons would be Hold Time (stops clock, holds time)
- a second set would be Hold Clock (stops clock, time advances). Labels would help to make this more clear to users.

Make sense? Or should I just look at the minimum. Note that the reason for the odd time results from Reset have probably been identified as per above.

RaymondW · Apr 7, 2024

Ultimately, I plan to add two more buttons to each display, "Continue" and "Resume", but I was trying to keep it simple initially and just get a Start/Stop function working.

For each of the three timers, the displayed time is the current time minus the start time.

The Start button starts all three timers and all three displays begin updating as they do now. Once the process is running, clicking Start again should do nothing.

Each Stop button freezes its displayed time. There needs to be a flag set for each timer to know if its display has been stopped (frozen) or if it's still updating (active). More on that below.

The Reset button should immediately set everything to zero and stop updating. Currently, it stops updating the displays but doesn't reset them to zero until it's pressed a second time. This wasn't intended but may be useful to see where everything stands before zeroing it all out.

It gets a little more complicated with the addition of the Continue and Resume buttons which are not yet included in the file:

Each Continue button only matters if its display was previously stopped (above). Its displayed time will be updated with its total time since Start (current time minus start time) as though it was never stopped. You might use this to pause the stopwatch to write down the accumulated time at a milestone. I hope that makes sense. Maybe this helps:

VBA Code:

Start                         Stop                    Continue
V                             V                       V
          111111111122222222223333333333444444444455555555556666666666777777777788888888889999999999
0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
^                             ^                       ^ 
The display increments        The display shows 30    The display picks up at 54 and continues

Each Resume button only matters if its display was previously stopped (above). The start time for this timer needs to be adjusted to ignore the paused time.

Code:

Start                       Stop                   Resume
V                           V                      V
          111111111122222222223333333333444444444455555555556666666666777777777788888888889999999999
0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
^                           ^                      ^ 
The display increments      The display shows 28   The start time is adjusted to its current value (0) PLUS
                                                   the current time MINUS the last displayed time.
                                                   The new start time for this timer is 0 + 51 - 28 = 23
                                                   The display shows 28 and continues

The attached flow chart might help as well. Thank you for your help.

RaymondW · Apr 9, 2024

I've posted an updated workbook with all of the buttons and the flowchart on a second sheet for anyone who's looking.

Stopwatch.xlsm

Logit · Apr 9, 2024

Your "STOP" buttons stop all three instead of one individual clock.

RaymondW · Apr 9, 2024

Yes. That's the first problem. I don't understand why it's doing that.

Three-lane Stopwatch?

RaymondW

New Member

Excel Facts

Micron

Well-known Member

RaymondW

New Member

Micron

Well-known Member

RaymondW

New Member

RaymondW

New Member

Logit

Well-known Member

RaymondW

New Member

Similar threads

Forum statistics

Share this page

Three-lane Stopwatch?

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

New Member

New Member

Well-known Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock