0

I have a very similar problem to this thread: Increment a cell value with a timer - tried adding a comment to that rather than posting a new thread, but didn't have reputation of 50, so here I am.

I have a timer in my Excel sheet in cell B7 which counts down, and I have buttons to start and stop the timer which work well. When the timer reaches zero, I would like the value in cell B8, currently set to 1, to increment by 1, and I would like it to do this each time the timer is reset and subsequently goes back down to zero.

The code below is what I have just for the timer itself, without trying to increment the value in B8:

Public interval As Date

Sub timer()

     interval = Now + TimeValue("00:00:01")

     If Range("B7").Value = 0 Then Exit Sub
     
     Range("B7") = Range("B7") - TimeValue("00:00:01")
     
     Application.OnTime interval, "timer"

 End Sub

Sub stop_timer()
     Application.OnTime EarliestTime:=interval, Procedure:="timer", Schedule:=False
 End Sub

The problem occurs when I try to change the timer macro to "If Range("B8").Value = 0 Then Range("B8").Value = Range("B8").Value+1

I've tried playing around with it a few different ways, but ultimately I get some type of syntax error or broken expression, or the start/stop buttons just stop working. I'm sure I'm missing something simple and/or there's probably another way to do this, but I know next-to-nothing about VBA and only managed to get this far through some searching online and lots of tinkering, so would appreciate help and guidance from anyone that is able to offer it.

Thanks!

user1824651
  • 31
  • 3

1 Answers1

0

Ok, I completely rewrote the code.

  1. Removed the recursive call to Timer.
  2. Changed check value to Zero based on changes to code.
  3. Changes Stop_Timer to merely change B8 to Zero then Call Check_Timer

This all works as written. What it won't do is let you stop the timer and then restart where your left off. If you need that funcionality we'll need more code. enter image description here

Dim Interval As Date

Sub Run_Timer()

   Interval = Now + TimeValue("00:00:01")
   Application.OnTime EarliestTime:=Interval, Procedure:="Check_Timer", Schedule:=True

End Sub 'Run_Timer()

Sub Check_Timer()

' Increment your counter before exiting.
     If ([B7]) = 0 Then
       [B8] = [B8] + 1
       Exit Sub
     End If
     
     [B7] = [B7] - TimeSerial(0, 0, 1)
     
     Call Run_Timer
     
End Sub 'Check_Timer

Sub Stop_Timer()

   [B7] = 0
   Check_Timer
     
End Sub 'Stop_Timer

HTH

RetiredGeek
  • 2,980
  • 1
  • 7
  • 21
  • When I copy that code and run it, I get a Compile Error: Syntax Error. – user1824651 Sep 20 '20 at 23:51
  • Changed the comment character and also changed it to look for 1 instead of 0, but still get the error. I would post screenshots, but I can't seem to figure out how to add attachments or paste anything in this comment box. – user1824651 Sep 21 '20 at 16:55
  • There was an errant character "I" in the original post also after the Then. Best to copy and paste over again to make sure it matches. Also make sure you enter the count down time as =TimeValue("00:00:00") in B7 or course placing the minutes/seconds as appropriate. – RetiredGeek Sep 21 '20 at 17:27
  • Thanks for continuing to help with this. Copied your code (you have the sub procedure name defined twice, FYI), and the timer now works and counts down, however when it reaches 0 and stops, it gives "Run-time error 1004, application-defined or object-defined error". Initially when setting up the timer I formatted the cell Category as Custom with type set to h:mm:ss (I wanted hh:mm:ss, but that wasn't a choice). I even tried changing the Categoryto Date and set the type to 13:50:55, but still get the same error. – user1824651 Sep 21 '20 at 17:53
  • Also for what it's worth, I noticed that with the above code, the stop button no longer works. And lastly, when the timer reaches zero and it throws the error, I noticed that it changes the cell from =TimeValue("00:00:00") to 12:00:00 AM in the formula bar (apparently this is expected behavior, but wonder if it's causing part of the issue). The value in the cell itself is 0:00:00 when it's done ticking down. – user1824651 Sep 21 '20 at 19:14