create a countdown timer in excel 2010 using VBA (excel)

Question

I'm using Excel 2010, and want to create a countdown timer in excel. The code will be started and stopped by using buttons with macros attached to them. The problem occurs when the "start" button is pushed. The code above is utilizing Cell "B1" as the input spot, because I was just trying to get it to work, but every time I tried, it would always say: "Cannot run the macro . The macro may not be available in this workbook or all macros may be disabled".

Yes I enabled all macros before putting this here. I want to be able to take user input, and to make that the time that the timer starts at instead of just using cell "B1".

'Macro for Starting the timer (attached to the start button)
Sub startTimer()
Application.OnTime Now + TimeValue("00:00:01"), "nextTick"
End Sub

'Macro for next second
Sub nextTick()
Sheet1.Range("B1").Value = Sheet1.Range("B1").Value - TimeValue("00:00:01")
startTimer
End Sub

'Macro for stopping the timer (attached to the end button)
Sub stopTimer()
Application.OnTime Now - TimeValue("00:00:01"), "nextTick", , False
End Sub

Is this code in a module code sheet or a sheet code sheet? Did you get there by right-clicking the worksheet's name tab and choose View Code? Did you open the VBE with [alt]+F11 and use the pull-downs to Insert ► Module? — user4039065
– user4039065, Commented May 11, 2016 at 21:18

Gary's Student · Accepted Answer · 2016-05-11 22:00:41Z

1

I modified your code very slightly and put it in a standard module:

Public Future As Double

Sub StartTimer()
    Future = Now + TimeSerial(0, 0, 1)
    Application.OnTime earliestTime:=Future, procedure:="nextTime", _
         schedule:=True
End Sub

Sub nextTime()
    Sheet1.Range("B1").Value = Sheet1.Range("B1").Value - TimeValue("00:00:01")
    StartTimer
End Sub

Sub StopTimer()
   On Error Resume Next
   Application.OnTime earliestTime:=Future, _
       procedure:="nextTime", schedule:=False
End Sub

Both StartTimer() and StopTimer() run just fine.

answered May 11, 2016 at 22:00

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

MTB Over a year ago

Thanks for taking the time to look at this, this works exactly as advertised.

MTB Over a year ago

I was wondering how I could get this to work during runtime, as in, i wanted to get user input and punch that in as a time variable, and then once all other modules were completed, then this module would be executed.

Gary's Student Over a year ago

@MTB I wrote some code a while back to do exactly what you describe. The code requested the time in seconds and then used it.

Collectives™ on Stack Overflow

create a countdown timer in excel 2010 using VBA (excel)

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related