Help: Multiple Live Timer (VBA Macro)

**zouk01** · 02-27-2014, 10:54 AM

Hi,

LiveTime.jpg

May I know what will be the VBA code to script the following:

1. When I enter a time value on cell B4, the timer shall start running live on C4.
2. When I click the stop button on D4, the live timer on C4 will stop and results will be reflected on E4 I.e. Results is from NOW() minus B4
3. When time hits 1hr 30 mins, it will show amber, 2 hrs show red, less than 1hr 30mins show green.

I tried to use simple OnTime() method but doesn't achieve the desired outcome. Moreover I can't seem to find a code for the button to stop the timer for the individual row and reflect the results.

Please advise.

C4 Formula is =TIMEVALUE(HOUR($B$1-B4)&":"&MINUTE($B$1-B4)&":"&SECOND($B$1-B4))

Please Login or Register  to view this content.

Thank you.

**LJMetzger** · 03-01-2014, 06:40 PM

Hi,

Most interesting problem. I was able to solve your problem using 5 timers. The main timer (Timer 0) is the time of day timer which runs once a second. The other 4 timers are used only to schedule the 'START' for each timer based on the time of day each timer is supposed to start. After the other timers run once, they are no longer needed. After Timer 1 thru 4 starts, then each becomes a 'stop watch' and can be polled to update the elapsed time by the 'Timer 0' loop.

By using only 'Timer 0', it solves your STOP problem. When the 'Stop' command button is pressed, a software flag is set so that 'stop watch' is no longer active.

Please forgive me, as I got carried away a little and added a few features. If you don't like any of them and are not able to remove them yourself, I will gladly remove the unwanted features for you which include:
a. A UserForm to change the 'Start Times'.
b. Additional color coding

Lewis