countdown timer

[don matteo]

Hey guys i have taken some code from online to create a countdown timer
and when it hits 00:00:00 a msg box pops up
but i want the box to pop up when the timer hits 00:05:00 (5 min remaining )
can anyone help?
thanks

Public gCount As Date
Sub Timer()
gCount = Now + TimeValue("00:00:01")
Application.OnTime gCount, "EndMessage"
End Sub
Sub EndMessage()
Dim xRng As Range
Set xRng = Application.ActiveSheet.Range("b10")
xRng.Value = xRng.Value - TimeSerial(0, 0, 1)
If xRng.Value <= 0 Then
MsgBox "SEND COMM ASAP"
Exit Sub
End If
Call Timer
End Sub


Sub StopClock()
    Application.OnTime EarliestTime:=gCount, Procedure:="endmessage", Schedule:=False
   
End Sub

 

[wideboydixon]

Public gCount As Date
Sub Timer()

gCount = Now + TimeValue("00:00:01")
Application.OnTime gCount, "EndMessage"

End Sub
Sub EndMessage()

Dim xRng As Range

Set xRng = Application.ActiveSheet.Range("b10")
xRng.Value = xRng.Value - TimeSerial(0, 0, 1)
If xRng.Value <= TimeSerial(0, 5, 0) Then
    MsgBox "SEND COMM ASAP"
Else
    Call Timer
End If

End Sub
Sub StopClock()
    
Application.OnTime EarliestTime:=gCount, Procedure:="endmessage", Schedule:=False
   
End Sub

WBD

 

[don matteo]

thank you! this works
but how do i get the clock to continue counting down after you acknowledge the msg box?

 

[wideboydixon]

Sorry; I've been away.

Public gCount As Date
Sub Timer()

gCount = Now + TimeValue("00:00:01")
Application.OnTime gCount, "EndMessage"

End Sub
Sub EndMessage()

Dim xRng As Range

Set xRng = Application.ActiveSheet.Range("b10")
xRng.Value = xRng.Value - TimeSerial(0, 0, 1)
If xRng.Value <= TimeSerial(0, 5, 0) Then MsgBox "SEND COMM ASAP"

Call Timer

End Sub
Sub StopClock()
    
Application.OnTime EarliestTime:=gCount, Procedure:="endmessage", Schedule:=False
   
End Sub

 

[don matteo]

hey no problem
thank you but that code doesnt work, the msg box keeps popping up because its under 5 min

 

[wideboydixon]

Ah. Of course. OK.

Public timerCell As Range
Public nextEvent As Date
Public showMessage As Boolean
Public Sub StartTimer()

Set timerCell = Application.ActiveSheet.Range("B10")
showMessage = True
Call Timer

End Sub
Public Sub Timer()

nextEvent = Now + TimeSerial(0, 0, 1)
Application.OnTime EarliestTime:=nextEvent, Procedure:="EndMessage"

End Sub
Public Sub EndMessage()

timerCell.Value = timerCell.Value - TimeSerial(0, 0, 1)
If timerCell.Value <= TimeSerial(0, 5, 0) And showMessage Then
    MsgBox "SEND COMM ASAP"
    showMessage = False
End If

If timerCell.Value > 0 Then Call Timer

End Sub
Public Sub StopTimer()
    
Application.OnTime EarliestTime:=nextEvent, Procedure:="EndMessage", Schedule:=False
   
End Sub

Put the time value in B10 and call StartTimer.

WBD