Countdown timer

[Guest]

I would like to create a countdown timer for an excel file to monitor the
time from now an a predetermined time as computed in the worksheet

 

[Bernie Deitrick]

Ira,

Name a cell TimeToGo, and in that cell enter the formula (watch line wrapping)

=IF(NOW()<TargetDate,INT(TargetDate-NOW()) & " days, " & TEXT(((TargetDate-NOW()) -
INT(TargetDate-NOW())),"hh:mm:ss"),"Done!")

Name another cell TargetDate, and in that cell either use a formula to calculate or directly enter
the Date/Time that you want to countdown to.

Then run StartIt and run StopIt if you want to stop the timer.

HTH,
Bernie
MS Excel MVP

Dim NextTime As Date
Public Running As Boolean

Sub StartIt()
Running = True
Update
End Sub

Sub Update()
NextTime = Time + TimeValue("00:00:01")
If Range("TimeToGo").Value <> "Done!" Then
Range("TimeToGo").Calculate
Application.OnTime NextTime, "Update"
End If
End Sub

Sub StopIt()
If Not Running Then Exit Sub
On Error Resume Next
Application.OnTime NextTime, "Update", schedule:=False
Running = False
End Sub

 

[Guest]

Bernie,

The code is realy great! It works like a charm. All i need now is a button
to start the timer and a pop up menu to show if the time has expired. I've
seen some excel files have buttons to start a macro and others with
customized popup screens for alerts and stuff.

Thanks!

Ira

 

[Bernie Deitrick]

Ira,

Do you need help with the buttons?

Bernie
MS Excel MVP

 

[Guest]

Bernie,

I was able to create customized buttons for a toolbar during my testing to
start and stop the macros. However i think it would be better if the buttons
existed on the worksheet itself since I would not be the only user of the
file.

With regards to the popup screens, i would like these to show data from
specific cells within worksheet when the timer is done.

Thanks for the help!

Ira

 

[Bernie Deitrick]

Use any drawing object and place two on the workbook, and assign the start and stop macros to them
by right-clicking and choosing "Assign macro"

As for the pop-up, change

If Range("TimeToGo").Value <> "Done!" Then
Range("TimeToGo").Calculate
Application.OnTime NextTime, "Update"
End If

To soemething along these lines:

If Range("TimeToGo").Value <> "Done!" Then
Range("TimeToGo").Calculate
Application.OnTime NextTime, "Update"
Else
MsgBox "The values that I want to show are" & Chr(10) & _
Range("A1").Value & Chr(10) & _
Range("A2").Value & Chr(10) & _
Range("A3").Value & Chr(10) & _
Range("A4").Value
End If

HTH,
Bernie
MS Excel MVP

 

[Guest]

Bernie,

thanks for the help. i was having a bit of difficulty showing the
information on the msg box. I'll give it a go and let you know the result.

Many thanks!

ira

 

[Guest]

Bernie,

Thanks again for the help! I've been using the timer for some time now and
have modifided it even.

Best regards,
Ira