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

You must place the code in the code module of the sheet, not in a regular module.

Open Excel, right click the sheet tab and select "View Code". Paste the code in there.

Andreas.

Answer 2

Thank you for your help. I've tried to follow what you instructed, but when on the run a report: Compile error: Invalid use of Me keyword

and there is no code for the start button.

If not too much trouble, I hope you can send me a sample file excel via email. I am a novice excel and VBA.

Answer 3

Okay, that's very easy.

Make a new file and copy all code below my name into the code module of the worksheet.

IMHO it's better for your purpose, because you can switch to other sheets if the counter is running.

Execute Sub Example_ShowTimer once, you did not use that code later, it's just for this example. The Change event should fire and the cell A2 is colored green.

Execute Sub ShowTimer. The counter starts, the cell is colored red by the Change event.

This sub is executed every second and updates the counter. If the endtime is reached, it stores 0 into the counter, the Change event fires and the cell is colored green again. Done.

Andreas.

Option Explicit

Dim TimerCounter As Range

Dim EndTime As Double

Private Sub Worksheet_Change(ByVal Target As Range)

  Select Case Target.Address(0, 0)

    'Counter cell changed?

    Case "A2"

      If Target = 0 Then

        'Green

        Target.Interior.Color = 5287936

      Else

        'Red

        Target.Interior.Color = 255

      End If

  End Select

End Sub

Sub Example_ShowTimer()

  'Setup a scenario for ShowTimer

  'The cell with the initial value

  With Range("A1")

    .NumberFormat = "[m]:ss"

    .Value = TimeSerial(0, 0, 10)

  End With

  'The cell to show the countdown

  With Range("A2")

    .NumberFormat = "[m]:ss"

    .Value = 0

  End With

End Sub

Sub ShowTimer()

  'Already initialized?

  If TimerCounter Is Nothing Then

    'Setup the cell to show the countdown

    Set TimerCounter = Range("A2")

    'Copy the start value

    'Note:

    '  This raises the Change event in the worksheet!

    '  You can color the cells here or in the change event

    TimerCounter = Range("A1")

    'Calculate the endtime

    EndTime = Now() + TimerCounter

    'Force excel to call ourself in one second

    Application.OnTime Now + TimeSerial(0, 0, 1), _

      Me.CodeName & ".ShowTimer"

    Exit Sub

  End If

  'To avoid an event every second!

  Application.EnableEvents = False

  'Store the remaining time into the cell

  TimerCounter = EndTime - Now()

  'Enable the events

  Application.EnableEvents = True

  'Countdown complete?

  If TimerCounter <= 0 Then

    'Be sure the cell shows zero

    'Note:

    '  This raises the Change event in the worksheet!

    '  You can color the cells here or in the change event

    TimerCounter = 0

    'Prepare for the next run

    Set TimerCounter = Nothing

  Else

    'Force excel to call ourself in one second

    Application.OnTime Now + TimeSerial(0, 0, 1), _

      Me.CodeName & ".ShowTimer"

  End If

End Sub

Answer 4

Sorry if my english is hard to understand. I mean count pullback nonrecurring, one cell to input the time (in minutes), one cell for the countdown display and a single button to start. When the countdown is complete region specific cell changes color index. Thank you.

Answer 5

Iam very interested your answer about countdown timer in excel, but I want not repeat with single commandbutton "START" and cell to input timer. I hope you can help me write VBA code for this cases. My excel 2010. Thanks