Solved Countup timer for Excel

July 27, 2013 at 10:42:45
Specs: Windows 7
I need a very simple timer for Excel 2007 with START and STOP buttons, showing the mm:ss spent on a certain task (serving customer).
Can it be done, please?
Detailed instructions/code are welcome - I have little or none knowledge.
Thanks in advance!

See More: Countup timer for Excel

July 27, 2013 at 11:38:28
✔ Best Answer
The code found at the link below seems to work.

I had to modify the Subs by removing the word Private in the three places it is used, but other than that, it works fine.

Create your buttons, name them as instructed in the response, and try running the code. If you get a "Cannot run macro..." error, make sure you have Macros enabled. If you still get the error, try removing the word Private from the code as I had to.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

July 27, 2013 at 16:01:59
Thanks a lot, works fine! Now - how can I create a copies of this 3-button group with a separate time counter each? The idea is to have about a dozen of those on one screen (means the same worksheet, which may create the problem, I think) to keep tracking each of 12 customers' serving times simultaneously. Is there a simple copy/paste way, or I have to write the code for each group?
Copy/paste works for different worksheets, but all my attempts to place them in one worksheet failed.
Please, help...

Report •

July 27, 2013 at 16:49:09
There may be an easier way than this, but right now I can't think of one, and unfortunately I don't have time to play around. I'm sure I could write some VBA that will make this easier, but like I said, this is the best I can come up with for now.

I copied my 3 button set and renamed all of them as follows:



I then duplicated all of the routines and edited them, adding a 1 or 2 after every variable and routine name as shown below. In addition, in the set of routines for Timer #2, I changed every occurrence of B3 to B4, since we need a separate cell for each timer.

You also have to be sure to Dim the new variables so that VBA doesn't get confused by having the same variable names used in different sets of routines.

The code below seem to work for 2 timers, therefore you should be able to replicate it for 12 timers. I will mention that since Excel can't really multitask, the 2 timers weren't as smooth as the single timer. While I trust that the time in each cell is right, they may not both change exactly on each second if the system is busy updating one cell or the other. I imagine that running 12 timers at once will make it appear even rougher, but I can't say for sure.

Dim StopTimer1           As Boolean
Dim SchdTime1            As Date
Dim Etime1               As Date
Dim StopTimer2           As Boolean
Dim SchdTime2            As Date
Dim Etime2               As Date
Const OneSec            As Date = 1 / 86400#

Sub ResetBtn1_Click()
    StopTimer1 = True
    Etime1 = 0
    [B3].Value = "00:00:00"
End Sub
Sub StartBtn1_Click()
   StopTimer1 = False
   SchdTime1 = Now()
   [B3].Value = Format(Etime1, "hh:mm:ss")
   Application.OnTime SchdTime1 + OneSec, "Sheet1.NextTick1"
End Sub
Sub StopBtn1_Click()
    StopTimer1 = True
End Sub

Sub NextTick1()
   If StopTimer1 Then
      'Don't reschedule update
      [B3].Value = Format(Etime1, "hh:mm:ss")
      SchdTime1 = SchdTime1 + OneSec
      Application.OnTime SchdTime1, "Sheet1.NextTick1"
      Etime1 = Etime1 + OneSec
   End If

End Sub

Sub ResetBtn2_Click()
    StopTimer2 = True
    Etime2 = 0
    [B4].Value = "00:00:00"
End Sub
Sub StartBtn2_Click()
   StopTimer2 = False
   SchdTime2 = Now()
   [B4].Value = Format(Etime2, "hh:mm:ss")
   Application.OnTime SchdTime2 + OneSec, "Sheet1.NextTick2"
End Sub
Sub StopBtn2_Click()
    StopTimer2 = True
End Sub

Sub NextTick2()
   If StopTimer2 Then
      'Don't reschedule update
      [B4].Value = Format(Etime2, "hh:mm:ss")
      SchdTime2 = SchdTime2 + OneSec
      Application.OnTime SchdTime2, "Sheet1.NextTick2"
      Etime2 = Etime2 + OneSec
   End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

July 28, 2013 at 03:59:00
Thank you very much!

Report •

Ask Question