Schedule a Sub to run at intervals in VBA

[Tony Woodhouse]

Dear All,

I have an Excel workbook with a variety of macros. I would like to run one
of my macros once every 30 seconds, while still allowing my user to use the
workbook all the time the macro isn't running.

I can't find any command to allow me to do this. I've seen the Wait()
function, but it doesn't allow user interaction while it is waiting. I've
also tried:-

Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sleep(30000)

But this also prevents the user interacting with the workbook during the
pause. Is there another "kernel32" type subroutine that will run a
specified Sub at a give time and allow interaction in the mean time?

Best wishes,
Tony W

 

[Dick Kusleika]

Tony

Use OnTime. Make the last line of MyProcedure

Application.OnTime Now + TimeValue("00:00:30"), "MyProcedure"

that will schedule it to run again in 30 seconds.

 

[Tony Woodhouse]

Works perfectly - many thanks
Tony Woodhouse - Rugby - England