Automating Macros

[Jar2112]

Does anyone know how I can set a macro to automaticly update with a set amount of time? Like every five minutes for example. I have the macro assigned to a button that I would like to not have to push constantly.

THANKS EVERYONE!!!! ray:

 

[scuzz]

Maybe you could delay the time that the macro will run, and loop the statement making it repeat?? dont ask me how you'd do that tho!!

 

[Ken Puls]

Try looking up OnTime either on this site, or in the VBA help. That should get you started.

HTH,

 

[dk]

Well, the 2 guys replied while I was writing up a simple example so I'll post it anyway:-

If you place all of this code into a module and run the procedure 'Main', the procedure DoSomething will run every 5 minutes. Call the StopIt procedure to cancel it.

Dim dteProcTime As Date

Sub Main()
    dteProcTime = Now() + TimeValue("00:05:00")
    Application.OnTime dteProcTime, "DoSomething"
End Sub


Sub DoSomething()
    Cells(1, 1) = "Hello.  The current time is " & Now
    Call Main
End Sub


Sub StopIt()
    Application.OnTime dteProcTime, "DoSomething", , False
End Sub

 

[simes]

came across this post as i have been trying to do the same thing...

didn't seem to work...or might have been me...

the macro seems to run only once as opposed to at regular intervals

pls help

 

[dk]

Hi Simes,

Are you running the "Main" macro? It should also work if you run the DoSomething macro so I'm not sure what you're doing wrong. Just to clarify, this macro will run every 5 minutes and will put the current time in cell A1 UNTIL you run the StopIt macro.

Let me know how you go mate