Repeat macro automatically each 10 min

Posted by Oli on March 14, 2001 5:54 PM

Is there a way that a macro can repeat itself each 10 minute without having to restart it manually ( i tried to make a loop, but it take all the resources and the computer become slow!). Or is it possible to start a macro every time the value of a cell change? How can i do that?
Thanx

Posted by Dave Hawley on March 15, 2001 2:51 AM

Hi Oli

Place this in a normal module:

Sub MyMacro()
MsgBox "hello, see you in 10 seconds :)"
Application.OnTime Now + TimeValue("00:00:15"), "MyMacro"
End Sub

Now place these in the Workbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime EarliestTime:=TimeValue("00:00:10"), _
Procedure:="MyMacro", Schedule:=False
End Sub

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

This will run MyMacro every 10 seconds, just change the time interval to suit.

Dave
OzGrid Business Applications