Timer on user form in Excel VBA

Question

I've got some old Excel VBA code where I want to run a task at regular intervals. If I were using VB6, I would have used a timer control.

I found the Application.OnTime() method, and it works well for code that's running in an Excel worksheet, but I can't make it work in a user form. The method never gets called.

How can I make Application.OnTime() call a method in a user form, or are there other ways to schedule code to run in VBA?

Answer 1

I found a workaround for this. If you write a method in a module that just calls a method in your user form, then you can schedule the module method using Application.OnTime().

Kind of a kludge, but it'll do unless somebody has a better suggestion.

Here's an example:

''//Here's the code that goes in the user form
Dim nextTriggerTime As Date

Private Sub UserForm_Initialize()
    ScheduleNextTrigger
End Sub

Private Sub UserForm_Terminate()
    Application.OnTime nextTriggerTime, "modUserformTimer.OnTimer", Schedule:=False
End Sub

Private Sub ScheduleNextTrigger()
    nextTriggerTime = Now + TimeValue("00:00:01")
    Application.OnTime nextTriggerTime, "modUserformTimer.OnTimer"
End Sub

Public Sub OnTimer()
    ''//... Trigger whatever task you want here

    ''//Then schedule it to run again
    ScheduleNextTrigger
End Sub

''// Now the code in the modUserformTimer module
Public Sub OnTimer()
    MyUserForm.OnTimer
End Sub