+ Reply to Thread
Results 1 to 4 of 4

Excel VBA - Multiple procedures in 1 sheet to set up multiple timers

Hybrid View

flyerspuck100 Excel VBA - Multiple... 10-26-2018, 11:01 AM
Logit Re: Excel VBA - Multiple... 10-26-2018, 12:50 PM
6StringJazzer Re: Excel VBA - Multiple... 10-26-2018, 12:52 PM
flyerspuck100 Re: Excel VBA - Multiple... 10-26-2018, 01:04 PM
  1. 10-26-2018, 11:01 AM #1
    flyerspuck100 is offline
    Registered User
    Join Date
    10-26-2018
    Location
    Philadelphia
    MS-Off Ver
    2016
    Posts
    2

    Cool Excel VBA - Multiple procedures in 1 sheet to set up multiple timers

    Not a programmer, came across a site that has a stopwatch timer.

    Works great, i wanted to add multiple timers to about 6 rows, so i modified the procedure below. While at first it appears to work the problem is the next row that starts a timer will start where the row before it left off. How can i isolate each timer to be independent?

    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
'This is the start of row 3

Private Sub CommandButton4_Click()
Dim StartTime, FinishTime, TotalTime, PauseTime
StopIt = False
ResetIt = False
If Range("C3") = 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("C3").Value = Format(hh, "00") & ":" & Format(MM, "00") & ":" & Format(SS, "00") & "." & Format(HM, "00")
    If ResetIt = True Then
      Range("C3") = 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 CommandButton5_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
  StopIt = True
End Sub
Private Sub CommandButton6_Click()
  Range("C3").Value = Format(0, "00") & ":" & Format(0, "00") & ":" & Format(0, "00") & "." & Format(0, "00")
  LastTime = 0
  ResetIt = True
End Sub

'This is the start of row 4

Private Sub CommandButton7_Click()
Dim StartTime, FinishTime, TotalTime, PauseTime
StopIt = False
ResetIt = False
If Range("C4") = 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("C4").Value = Format(hh, "00") & ":" & Format(MM, "00") & ":" & Format(SS, "00") & "." & Format(HM, "00")
    If ResetIt = True Then
      Range("C4") = 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 CommandButton8_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
  StopIt = True
End Sub
Private Sub CommandButton9_Click()
  Range("C4").Value = Format(0, "00") & ":" & Format(0, "00") & ":" & Format(0, "00") & "." & Format(0, "00")
  LastTime = 0
  ResetIt = True
End Sub
'This is the start of row 5

Private Sub CommandButton10_Click()
Dim StartTime, FinishTime, TotalTime, PauseTime
StopIt = False
ResetIt = False
If Range("C5") = 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("C5").Value = Format(hh, "00") & ":" & Format(MM, "00") & ":" & Format(SS, "00") & "." & Format(HM, "00")
    If ResetIt = True Then
      Range("C5") = 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 CommandButton11_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
  StopIt = True
End Sub
Private Sub CommandButton12_Click()
  Range("C5").Value = Format(0, "00") & ":" & Format(0, "00") & ":" & Format(0, "00") & "." & Format(0, "00")
  LastTime = 0
  ResetIt = True
End Sub

'This is the start of row 6

Private Sub CommandButton13_Click()
Dim StartTime, FinishTime, TotalTime, PauseTime
StopIt = False
ResetIt = False
If Range("C6") = 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("C6").Value = Format(hh, "00") & ":" & Format(MM, "00") & ":" & Format(SS, "00") & "." & Format(HM, "00")
    If ResetIt = True Then
      Range("C6") = 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 CommandButton14_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
  StopIt = True
End Sub
Private Sub CommandButton15_Click()
  Range("C6").Value = Format(0, "00") & ":" & Format(0, "00") & ":" & Format(0, "00") & "." & Format(0, "00")
  LastTime = 0
  ResetIt = True
End Sub
'This is the start of row 7

Private Sub CommandButton16_Click()
Dim StartTime, FinishTime, TotalTime, PauseTime
StopIt = False
ResetIt = False
If Range("C7") = 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("C7").Value = Format(hh, "00") & ":" & Format(MM, "00") & ":" & Format(SS, "00") & "." & Format(HM, "00")
    If ResetIt = True Then
      Range("C7") = 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 CommandButton17_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
  StopIt = True
End Sub
Private Sub CommandButton18_Click()
  Range("C7").Value = Format(0, "00") & ":" & Format(0, "00") & ":" & Format(0, "00") & "." & Format(0, "00")
  LastTime = 0
  ResetIt = True
End Sub

'This is the start of row 8

Private Sub CommandButton19_Click()
Dim StartTime, FinishTime, TotalTime, PauseTime
StopIt = False
ResetIt = False
If Range("C8") = 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("C8").Value = Format(hh, "00") & ":" & Format(MM, "00") & ":" & Format(SS, "00") & "." & Format(HM, "00")
    If ResetIt = True Then
      Range("C8") = 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 CommandButton20_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
  StopIt = True
End Sub
Private Sub CommandButton21_Click()
  Range("C8").Value = Format(0, "00") & ":" & Format(0, "00") & ":" & Format(0, "00") & "." & Format(0, "00")
  LastTime = 0
  ResetIt = True
End Sub
'This is the start of row 9

Private Sub CommandButton22_Click()
Dim StartTime, FinishTime, TotalTime, PauseTime
StopIt = False
ResetIt = False
If Range("C9") = 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("C9").Value = Format(hh, "00") & ":" & Format(MM, "00") & ":" & Format(SS, "00") & "." & Format(HM, "00")
    If ResetIt = True Then
      Range("C9") = 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 CommandButton23_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
  StopIt = True
End Sub
Private Sub CommandButton24_Click()
  Range("C9").Value = Format(0, "00") & ":" & Format(0, "00") & ":" & Format(0, "00") & "." & Format(0, "00")
  LastTime = 0
  ResetIt = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub
    Reply With QuoteRegister To Reply
  2. 10-26-2018, 12:50 PM #2
    Logit is offline
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    7,396

    Re: Excel VBA - Multiple procedures in 1 sheet to set up multiple timers

    .
    Here is an example project that you can draw from. This is only a portion of the code :

    Private Sub CommandButton1_Click()
    Range("F3") = "00:00:00"
End Sub
Private Sub CommandButton2_Click()
    Range("H3") = "00:00:00"
End Sub
Private Sub CommandButton3_Click()
    Range("J3") = "00:00:00"
End Sub
Private Sub Timer1_start_Click()
    If Timer1_start.Caption = "Start" Then
        StartTimer1
    Else
        StopTimer1
    End If
End Sub
Private Sub Timer2_start_Click()
    If Timer2_start.Caption = "Start" Then
        StartTimer2
    Else
        StopTimer2
    End If
End Sub
Private Sub Timer3_start_Click()
    If Timer3_start.Caption = "Start" Then
        StartTimer3
    Else
        StopTimer3
    End If
End Sub
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  3. 10-26-2018, 12:52 PM #3
    6StringJazzer is offline
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2411
    Posts
    26,747

    Re: Excel VBA - Multiple procedures in 1 sheet to set up multiple timers

    These three variables are declared at the top of your module, which means they are global and shared by all procedures:
    Public StopIt As Boolean
Public ResetIt As Boolean
Public LastTime
    To fix your problem you need to declare a separate set of StopIt, ResetIt, and LastTime for each timer, instead of all the timers sharing the same variables.

    The code you grabbed is a somewhat unorthodox way of doing timers because each timer is in a continuous loop to determine if it's time to advance the timer to the next second. I did not test it but it seems like this would chew up a lot of CPU time. If it works for your purposes then I'll leave well enough alone.
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]
    Reply With QuoteRegister To Reply
  4. 10-26-2018, 01:04 PM #4
    flyerspuck100 is offline
    Registered User
    Join Date
    10-26-2018
    Location
    Philadelphia
    MS-Off Ver
    2016
    Posts
    2

    Re: Excel VBA - Multiple procedures in 1 sheet to set up multiple timers

    makes sense, but when i add private and a new variable name for the next timer i get an error. appears you cannot add another declared variable after the first procedure? what would it take for you 6StringJazzer to write the code?
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. multiple timers
    By kadlaw in forum Excel General
    Replies: 2
    Last Post: 11-14-2017, 04:56 AM
  2. Multiple timers
    By OillyBob55 in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 08-17-2017, 04:38 PM
  3. [SOLVED] Multiple countdown timers and pop-up messages/alarms on a single sheet
    By charliethechamp in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-21-2015, 06:21 AM
  4. Multiple Timers within EXcel
    By Janto724 in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 04-26-2013, 09:07 AM
  5. multiple countdown timers in excel?
    By hollandrob81 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-06-2013, 04:12 PM
  6. Setup Multiple Timers
    By jomili in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-02-2010, 04:38 PM
  7. Running multiple timers
    By tropy in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-17-2006, 09:44 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1