Sub AutoRunMe()
Application.OnTime Now + TimeSerial(0, 0, 10), "MyMacro"
End Sub

Sub MyMacro()
MsgBox Now
Call AutoRunMe
End Sub


That code isn't a good idea, because it doesn't allow you to cancel
the pending on time event. The timer rescheduling will turn off only
if you completely quit Excel. Closing the workbook isn't enough -Excel
will reopen it when the timer pops.

Instead, create a module level variable and assign to it the value
when the code should run:

Public RunWhen As Double

Sub RunMe()
' code
RunWhen = Now + TimeSerial(0, 0, 10)
Application.OnTime RunWhen, "RunMe", , True
End Sub

To cancel a timer, you must specify the *exact* time that it is
schedule to pop. You can do this only if you have stored the time
value in a module-scope variable.

Sub StopTimer()
Application.OnTime RunWhen, "RunMe", , False
End Sub

I have extensive notes about OnTime at
http://www.cpearson.com/excel/OnTime.aspx

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)






On Thu, 16 Apr 2009 09:50:02 -0700, Jacob Skaria
wrote:

Hi Steve

Insert a module and paste the below procedures and try.

Sub AutoRunMe()
Application.OnTime Now + TimeSerial(0, 0, 10), "MyMacro"
End Sub

Sub MyMacro()
MsgBox Now
Call AutoRunMe
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Steve" wrote:

Morning all.
I'm interested in doing an "autorun" macro that will run every few minutes,
to every couple of hours.
I've looked around here on the newsgroup, and the various forums, and found
some examples, but in the application of them, I don't seem to get what I
want. See code....

Sub AutoRunMe()
application.ontime now + timeserial(0, 0, 10), "MyMacro"
end sub

In my reading, it states that the above code will run every 10 seconds,
following an initial start time of "now."

If however I do this:

application.ontime timeserial( 0, 0, 10), "MyMacro"

it'll only run at 10 seconds past midnight.

I also tried now + timevalue("00:00:10"); it's not repeating every 10
seconds either.


What I'm finding is that the first one does not run every ten seconds-- I
tried it just to make sure it'll do what I need-- and I cannot find anything
else that will allow me to run the macro every few minutes or hours, as I
decide.

I realize that I don't entirely understand it, so I wanted to ask-- how do I
create a macro that'll run every few minutes or so?

Thank you.