Search code examples
excelvbaintervalsuserformontime

Application.OnTime doesn't work with userform

I've got a problem with a piece of code:

Private Sub cyclic()

Static i As Integer
i = i + 1

Cells(i, 11) = i
Open source For Append As #1
Write #1, Cells(i, 11)
Close #1

Application.Wait (Now + TimeValue("0:00:01"))

Open source For Input As #1
Do While Not EOF(1)
Input #1, x1
Loop
Cells(i, 1) = x1
Close #1

Application.OnTime Now + TimeValue("00:00:03"), "cyclic"

End Sub

It's pretty much like this. It works perfectly from the level of worksheets alt+f8 macros, but when I want to make use of it with a Userform just by clicking a CommandButton it doesn't happen periodicaly with a 4 seconds interval, but do only one loop. If I want to fill more cells with my numbers I have to click click click and click the damn button. I wish it was more automatic.

Here is a piece of code from Userform button I am using to call the cyclic function. It should be repeating, but it's not.

Private Sub cbCyclic_Click()
cyclic
End Sub

What am I doing wrong?

Solution

  • A procedure referenced from Application.OnTime must reside in a standard code module. It cannot be located in a class module, and userforms are specialized classes.

    The procedure being called in the standard code module must be Public.

    Your best bet is to relocate cyclic() to a standard code module and make it Public. Your command button click event procedure in the userform should then work with no alteration.