VBA code to run repeat countdown timer in excel

Question

All

I require some VBA code which will run a countdown timer in a cell in an excel sheet. In our factory we have a Takt time which dictates how often a finished product is produced. Basically if we have 1000 mins available work time each day and we have to build 100 units per day then our takt time would be 10 mins. One finished product should come off the assembly line every 10 mins.

I would like to have an excel spreadhseet which would be linked to a large flat screen monitor which will display daily production information. In the sheet I would like to have a countdown timer which will run based on the takt time. If like the example above the takt time is 10 mins I would like the worksheet cell to countdown from  20mins to 0.and when it gets to 0 it would refresh and start from 20 mins again automatically. I would like the macro to be linked to a command button called 'Start' which would be clicked at the start of the shift, and the countdown will run repeatidly all day and then a second button called 'Stop' which will stop the timer. I would also need to ensure that the spreadsheet can be edited and other cells can be populated with information / data whilst the countdown is running. 

Is any of this possible? I have seen some examples of countdown timers but nothing to what I really need. Any help would be great. I have office 2003 but most employees here have 2000 so it would need to be compatible with both versions. Thanks.

Answer 1

Thank you so much. Ive just spoken to my project manager who is happy with the general concept of the timer. What we ideally want to do is everytime the copuntdown goes through one loop cycle then it will automtically update other cells. Lets say the timer is set for 10 mins, everytime the countdown goes through once cyle we would like to automatically record the number of units that should have been built so far during the day i.e. the target is automatically recorded. So for example after one hour we should have completed 6 machines so I want the cell C1 say to show the number 6, 10 mins later i want it to show 7, after the next countdown it will show 8 etc..

Is this possible at all. I may need to add 2 more buttons called Pause Timer and Restart Timer. The Stop Button will only be used at the end of the day. We need to be able to Pause the loop timer and restart it again when people are at lunch or tea break.

Answer 2

Am 17.08.2010 14:40, schrieb dazzag82:

Fantastic great job...just what I need...is there anything which can be added to the Stop Timer code which will set the value to zero in cell A1?

Of course, very easy:

Private Sub CommandButton2_Click()

  'Stops the timer

  StopTimer = True

  'Reset cell to zero

  TimerCell = 0

End Sub

Andreas.

Answer 3

See www.cpearson.com/Excel/OnTime.aspx for a complete discussion and example code for using Excel's OnTime method.

---

Cordially, Chip Pearson Microsoft MVP, Excel Pearson Software Consulting, LLC www.cpearson.com

Answer 4

Fantastic great job...just what I need...is there anything which can be added to the Stop Timer code which will set the value to zero in cell A1?

Darren

Answer 5

Hey thats great. Working well however it is not possible to edit the spreadsheet while the timer is running.

I will need to be able to enter data into other cells but when i try to tpe something it automatically stops the timer. The timer will be running in a loop for hours at a time and during that time other cells need to be popoualted with other production information.

Your code above has the line 'Const Minutes = 20' to set the timer for 20 mins. Instead of having the time stated in the code is it possible to specify the takt time in a cell in the sheet and have the code reference the cell. The takt time could change. This week it could be 10 mins next week it could be 13 mins. Takt time changes based on the number of orders and demand for product. The guys using the spreadsheet would not be advanced in excel so I need to make it as user friendly as possible. That they can specify the takt time in one cell and then just click the timer

Great help so far. Thanks.