countdown in vba userform triggered by a function

Question

I would like to start a countdown form from a running code. The problem is if I state

> TimerForm.Show

Then the user form is shown, however the countdown is not triggered. How can I trigger a countdown simply from a function where the countdown is shown in the user form TimerLabel, like below

TimerForm.TimerLabel.Caption = Format(TimeSerial(0, 0, nTime), "hh:mm:ss") & " seconds "

nTime would then be decreased from an initial value of 30 seconds.

The answer Below totally solves the problem. The cancel button for the Timer should be

    Private Sub CancelTimer_Click()

Application.OnTime EarliestTime:=earliest, _
                       Procedure:=handler, _
                       schedule:=False
Unload Me
End
End Sub

If not the Application.OnTime continues running in the background.

Answer 1

Do not know if understand the question correctly, but here are some thouhts. HTH

Standard module code

Option Explicit

Public Sub test()
    ' One possibility with modeless user form
    ' In this case it is possible to start timer after 
    ' timer form was displayed, because the form is modeless
    ' TimerForm.Show vbModeless
    ' TimerForm.StartTimer

    ' Other possiblity with modal user form
    ' In this case the StartTimer has to be called 
    ' from inside of user form because the form is modal.
    TimerForm.Show
End Sub

Public Sub TimerElapsed()
    TimerForm.OnTimerElapsed
End Sub

TimerForm class module code

' More info about OnTime:
' http://www.cpearson.com/excel/ontime.aspx

Option Explicit

Private Const interval As Integer = 1 ' second
Private Const countdownInit As Integer = 30 ' seconds
Private Const handler As String = "TimerElapsed"
Private earliest As Double
Private countdown As Integer

Private Sub UserForm_Initialize()
    countdown = countdownInit
    StartTimer
End Sub

Private Sub Cancel_Click()
    StopTimer
    Unload Me
End Sub

Public Sub StartTimer()
    earliest = Now + TimeSerial(0, 0, interval)

    Application.OnTime EarliestTime:=earliest, _
                       Procedure:=handler, _
                       Schedule:=True
End Sub

Public Sub StopTimer()
    On Error Resume Next
    countdown = 0
    Application.OnTime EarliestTime:=earliest, _
                       Procedure:=handler, _
                       Schedule:=False
End Sub

Public Sub OnTimerElapsed()
    If countdown <= 0 Then
        Me.TimerLabel.Caption = "00:00:00 seconds "
        Exit Sub
    End If

    Dim timerInfo As String
    timerInfo = Format(TimeSerial(0, 0, countdown), "hh:mm:ss")
    Me.TimerLabel.Caption = timerInfo & " seconds "
    countdown = countdown - interval

    StartTimer ' <--- 'How can I trigger a countdown simply 
               ' from a function where the countdown is shown
               ' in the user form TimerLabel ...' 
               ' Here the OnTime is re-scheduled for next call.
End Sub