Workbook code
Private Sub Workbook_Open()
intCountdown = 30
sTimeLonger = Now + TimeValue("00:01:00")
sTimeCountDown = Now + TimeValue("00:00:30")
Application.OnTime sTimeLonger, "OneMinuteInactive", , True
Application.OnTime sTimeCountDown, "ShowCountDown", , True
End Sub
Module code
Public sTimeLonger As Date
Public sTimeCountDown As Date
Public sTimeCountDownOneSeconds As Date
Public intCountdown As Integer
Public Sub OneMinuteInactive()
MsgBox "Hello"
Application.OnTime sTimeLonger, "OneMinuteInactive", , False
End Sub
Public Sub ShowCountDown()
sTimeCountDownOneSeconds = Now + TimeValue("00:00:01")
Application.OnTime sTimeCountDownOneSeconds, "DisplayCountdown", , True
End Sub
Public Sub DisplayCountdown()
Range("a1").Value = intCountdown
intCountdown = intCountdown - 1
ShowCountDown
End Sub
This is for 1minute main delay, 30 seconds for countdown. You will also need to stop the countdown when the counter gets to 0
Bookmarks