Timer in Excel

Hello,

I'm trying to create a timer in excel that starts when a cell in range A2:A8 = "Start Timer".

I've managed to get it to work using this code here, although, I'd like it to only work in the range A2:A8.

Also, is it possible to have the time difference update automatically every minute.

Please Login or Register  to view this content.

Attached, is a copy of the spreadsheet I'm working on.

Any help would be appreciated.

Timer.xlsm

Originally Posted by 6StringJazzer

XeRo Solus has addressed your question about what cells to consider, but your other question regarding the timer is unclear. I am not sure what you have managed to get working because your file has no code to implement a timer so it looks like we're starting from scratch here. Also, your data shows End Times that are earlier than the Start Times, so that adds to the confusion.

So you want to start a timer when the string "Start Timer" appears in A2:A8. What stops the timer?

Do you need a separate timer for each row?

Do you want to blank out the End Time when a timer starts for that row, until the timer stops?

"Also, is it possible to have the time difference update automatically every minute." If the timer is running, what is the definition of the time difference? That is, if the timer hasn't stopped yet, there is no End Time and therefore "time difference" is meaningless.

By the way, typing a string into a cell can introduce human error. If it's important to synchronize your Excel timer with some real-world event, a typo will screw it up. You may want to consider a simpler way to start the timer, such as typing any character in column A.

1. So to answer your questions. I would like to start the timer when the string "Start Timer" appears in A2:A8. Whenever the range A2:A8 is not equal to "Start Timer", I want to stop the timer.

2. Yes I need a separate timer for each row. I will possibly have up to 100 of these rows, so I'm not too sure how much CPU it's going to utilize.

3. Right now, I've made the End Time = NOW(). The difference in time is the timer. The issue though with this, is that it doesn't update. It doesn't matter if the End Time is blanked out, as I can just hide this column.

4. I'm happy to have the timer tick every minute, as opposed to every second.

With regards to the string, I plan on having a data validation that starts the timer.

OK, now I have a better idea of what you are doing. I hadn't taken the time to look at your formulas, just focused on the timer aspect.

You don't need 100 timers running. You need one timer that forces a recalculate once every minute. The code that manages the timer is in a new module called RunTimer. The first time the user select Start Timer in any row, the timer starts running. It does not stop until you exit Excel. If you need to modify your code to force the timer to stop, I have also provided a CancelTimer sub.

The strategy is that the start time is updated when the user selects Start Timer, and the End Time remains a formula. When the user selects End Timer, the formula is converted to a value. If the user selects Start Timer again for the same row, the End Time is converted back to the formula. In the meantime the timer is recalculating the sheet once per minute to update the End Time formulas.

Also, your original question was that you only wanted to look at A2:A8 but you said there could be 100 rows. If you add more rows after A8 remember to update the code.

I should probably add that in VBA, you can set an event to occur in the future. When you request the event, tou provide the name of a Sub to call when you want that event to happen. In this case, the Sub calls itself to simply repeat the event once every minute forever, until Excel is closed.

Originally Posted by 6StringJazzer

I should probably add that in VBA, you can set an event to occur in the future. When you request the event, tou provide the name of a Sub to call when you want that event to happen. In this case, the Sub calls itself to simply repeat the event once every minute forever, until Excel is closed.

Wow thank you so much. That's exactly what I was after. Works perfectly.