Display a countdown timer

[HAL]

Is it possible for A1 to display a time countdown in mm:ss
by adding to this:

Sub Autpen()
Application.OnTime Now + TimeValue("00:05:00"), "Closeit"
End Sub

Sub Closeit()
With ActiveWorkbook
.Save
.Close
End With
End Sub

TIA

 

[Bernie Deitrick]

HAL,

Format A1 for mm:ss, then use:

Dim myTime As Date

Sub Autpen()
Application.OnTime Now + TimeValue("00:05:00"), "Closeit"
myTime = Now + TimeValue("00:05:00")
Application.OnTime Now + TimeValue("00:00:01"), "TimeA1"
End Sub

Sub Closeit()
With ActiveWorkbook
.Save
.Close
End With
End Sub

Sub TimeA1()
Range("A1").Value = myTime - Now
Application.OnTime Now + TimeValue("00:00:01"), "TimeA1"
End Sub

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

HAL,

Actually, use this, or else the file may be reopened:

Dim myTime1 As Date
Dim myTime2 As Date

Sub Autpen()
myTime1 = Now + TimeValue("00:05:00")
Application.OnTime myTime1, "Closeit"
myTime2 = Now + TimeValue("00:00:01")
Application.OnTime myTime2, "TimeA1"
End Sub

Sub Closeit()
Application.OnTime myTime2, "TimeA1", , False
With ActiveWorkbook
.Save
.Close
End With
End Sub

Sub TimeA1()
Range("A1").Value = myTime1 - Now
myTime2 = Now + TimeValue("00:00:01")
Application.OnTime myTime2, "TimeA1"
End Sub


HTH,
Bernie
MS Excel MVP

 

[HAL]

Thank you very much! Much appreciated!

-----Original Message-----
HAL,

Actually, use this, or else the file may be reopened:

Dim myTime1 As Date
Dim myTime2 As Date

Sub Autpen()
myTime1 = Now + TimeValue("00:05:00")
Application.OnTime myTime1, "Closeit"
myTime2 = Now + TimeValue("00:00:01")
Application.OnTime myTime2, "TimeA1"
End Sub

Sub Closeit()
Application.OnTime myTime2, "TimeA1", , False
With ActiveWorkbook
.Save
.Close
End With
End Sub

Sub TimeA1()
Range("A1").Value = myTime1 - Now
myTime2 = Now + TimeValue("00:00:01")
Application.OnTime myTime2, "TimeA1"
End Sub


HTH,
Bernie
MS Excel MVP





.