# Simple count up timer to monitor how long a cell value = 1

June 21, 2017 at 05:37:18
Specs: Windows 7
 I am trying to make a simple count up timer (hh:mm:ss) to monitor how long a cell value is 1 and stop when it is 0 that can be reset from another macro that I schedule to run at a certain time.I am not a programmer but am learning.Thanks

See More: Simple count up timer to monitor how long a cell value = 1

#1
June 21, 2017 at 06:31:40
 I am assuming this will be in Excel?This can be achived with vba, when the cell value reaches 0 should the timer value hold until you reset?How many cells are you monitoring for 0 and 1 values?

#2
June 21, 2017 at 08:01:48
 Sorry I should have been more clearWhen the cell value is 1 the timer will increaseWhen the cell value is 0 the timer will stopthe timer will accumulate until resetThis will be in ExcelThanks for the quick response

#3
June 21, 2017 at 10:25:40
 So, lets take cell A1 for example for the 1 or 0 value, and B1 for the timer.Ok say A1 = 1B1 will keep on incrementing in seconds until A1 becomes 0 again, is this correct?I'll have a play tomorrow

Related Solutions

#4
June 21, 2017 at 12:51:47
 Yes thats perfectI will need to call the sub reset from another macro but that everything Thanks

#5
June 22, 2017 at 01:50:26
 Ok, here is an attempt, you will need to add three buttons to sheet1 (make sure it is sheet1 for now)The buttons will need to be namedCmdStartCmdStopCmdResetCell C7 will have the value 1 or 0 (ensure it is 1 when you press start otherwise it wont start counting)Cell H7 will need to be formatted as time and the value 00:00:00 within itPaste the below code into sheet1 moduleEnter 1 in Cell C7 Press startYou can now either press Stop OR change the value in C7 from 1 to 0 and the count will stop and the time will be held in H7. It is not perfect but lets play around and see what we can do with it.```Dim StopTimer As Boolean Dim SchdTime As Date Dim CountTimer As Date Dim Etime As Date Const OneSec As Date = 1 / 86400# Private Sub CmdStart_Click() StopTimer = False SchdTime = Now() CountTimer = "00:00:00" Application.OnTime SchdTime + OneSec, "Sheet1.NextTick" End Sub Private Sub CmdStop_Click() StopTimer = True Beep End Sub Private Sub CmdReset_Click() StopTimer = True Etime = 0 Range("C7") = 1 Range("H7") = "00:00:00" End Sub Sub NextTick() If StopTimer Or Range("C7") <> 1 Then Else AppActivate Application.Caption SchdTime = SchdTime + OneSec CountTimer = CountTimer + OneSec Range("H7") = CountTimer Application.OnTime SchdTime, "Sheet1.NextTick" Etime = Etime + OneSec End If End Sub ```

#6
June 22, 2017 at 07:40:39
 Sorry I forgot to mention, This spread sheet runs completely untended, the 0 or 1 value is coming from a remote location

#7
June 22, 2017 at 08:13:16
 Depends on what you mean by "untended" and "remote location," but this is sounding more like you need a simple program, and not an Excel macro. AlwaysWillingToLearn: That timer's going to be fragile. You're reliant on "NextTick" always firing on time, which Windows doesn't guarantee and Excel has known periods where it won't execute VBA code. To do what you're attempting, you need more persistent information than you have. Ideally when the timer started, and the value of the timer at that time. EDIT: I feel VBA's second representation doesn't actually perfectly represent a second since it's using floating point math, so you can probably expect some drift in the timer. Same persistent information would solve that issue. message edited by Razor2.3

#8
June 22, 2017 at 08:25:17
 Hi Razor,I agree, it was not a perfect solution but in the absence of anything else i thought id try. I have used Excel timer on many project but only where accuracy isn't absolutely paramount. Otherwise i tend to use vb6, its old but works for me. Other than Excel vba i have no other solution.