Keep Timer Going While Altering Workbook

[roadkill]

I am using the VB Timer function to feed a countdown timer (displayed in a
cell) and want to be able to edit my Workbook w/o stopping the timer. I've
tried using DoEvents in a Do loop but as soon as I edit a cell the timer
stops and I have to restart the macro to restart the timer. Is there a way
around this?

Thanks,
Will

 

[Vergel Adriano]

Will,

Maybe you can use the OnTime function. Something like this:

1. Create a new workbook.
2. Add a new module in the VB project and paste the code below.
3. Run the StartTimer procedure. It should start a 30 second timer and put
the remaining seconds in cell A1 of Sheet1.

One problem is that it stops updating while on edit mode.



Option Explicit
Private dTimerEnd As Date

Sub StartTimer()
Dim iSeconds As Integer

iSeconds = 30

dTimerEnd = Now + TimeSerial(0, 0, iSeconds)
Worksheets("Sheet1").Range("A1").Value = iSeconds

Application.OnTime EarliestTime:=Now + TimeValue("00:00:1"), _
procedure:="CountDown"
End Sub

Sub CountDown()
Dim iSecondsLeft As Integer

iSecondsLeft = CInt((dTimerEnd - Now) * 86400)

If iSecondsLeft <= 0 Then iSecondsLeft = 0

Worksheets("Sheet1").Range("A1").Value = iSecondsLeft

If iSecondsLeft > 0 Then
'keep going
Application.OnTime EarliestTime:=Now + TimeValue("00:00:1"), _
procedure:="CountDown"
End If
End Sub