Run Macro every 5secs

[dgavin]

Can someone tell me how i can run my workbook macro once every 5secs, continuously.

Many Thanks

 

[VoG]

Take a look at the OnTime method http://www.ozgrid.com/Excel/run-macro-on-time.htm

Edit: P.S. Here's an example running every second http://www.mrexcel.com/forum/showthread.php?t=532241

 

[dgavin]

Thanks,

I'm using the ozgrid method, but i get an error in my module. The "Now" is highlighted and error message only comments appear after End Sub, End Function or End Property

Can you help ?


Public dTime As Date
Sub Reset_Staking_Plan()

End Sub

dTime = Now + TimeValue("00:00:05")
Application.OnTime dTime, "Reset_Staking_Plan"

' Reset_Staking_Plan Macro
' Macro recorded 28/11/2007 by
'
Range("K19").Select
ActiveCell.FormulaR1C1 = "=R[2]C"
Range("K20").Select
ActiveCell.FormulaR1C1 = "=R[-18]C[2]"
Range("K21").Select

End Sub

 

[VoG]

Try

Public dTime As Date
Sub Reset_Staking_Plan()

dTime = Now + TimeValue("00:00:05")
Application.OnTime dTime, "Reset_Staking_Plan"

' Reset_Staking_Plan Macro
' Macro recorded 28/11/2007 by
'
Range("K19").FormulaR1C1 = "=R[2]C"
Range("K20").FormulaR1C1 = "=R[-18]C[2]"

End Sub

 

[dgavin]

perfect thanks