Run macro after every 10 seconds, facing problem after 1-2 runs

[ThrottleWorks]

Hi,

I am trying to run a macro after every 10 seconds.
I am using following code to call the macro.

Private Sub Workbook_Open()
    TmToRn = Now + TimeValue("00:00:10")
    Application.OnTime TmToRn, "Time_Calculator"
End Sub

When I open the file, the macro will run for at the max 2 times.
It is supposed to run after every 10 seconds, this does not happen, do not know what my mistake is.

I have also attached a file for reference.

Can anyone help me in this please.

 

[Marc L]

Hi,​

​

and no ! The procedure is not supposed to run after every 10 seconds as you programmed it ‼​

You call it only once ! You forgot to recall it ‼​

​

So the two lines in the procedure Workbook_Open must be at the end of the procedure Time_Calculator !​

And just call it in the Workbook_Open …​

 

[Luke M]

Only the Workbook_Open macro currently has a time delay. You should have one in the TIme_Calculator macro as well, as a recursive call.

Sub Time_Calculator()
    DoEvents
    ActiveSheet.Calculate
    Dim MyShp As Shape
    Set MyShp = ActiveSheet.Shapes("MyShp2")
    MyShp.IncrementRotation 1
    'Run again in 10 seconds
    TmToRn = Now + TimeValue("00:00:10")
    Application.OnTime TmToRn, "Time_Calculator"
End Sub

 

[ThrottleWorks]

Hi Marc Sir & Luke Sir, sorry for late reply.

Luke Sir, it is working, I have included these two lines in the code now.

Thanks a lot for the help.