• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 651
  • Last Modified:

run macro at 10 mins past the hour

I want to run an excel macro at 10minutes past every hour while the workbook is open.  Is there a way to do this?
2 Solutions
Arno KosterCommented:
add a module to the workbook, and put the macro code in it.
Make it a public sub.
The sub can be called by using application.ontime

Sub do_something()
    MsgBox "macro has been called"
   '-- schedule for next hour
   Application.OnTime TimeSerial(Hour(Now)+1, 10, 0), "do_something"
End Sub

Sub schedule_at_10_past()

    Application.OnTime TimeSerial(Hour(Now), 10, 0), "do_something"

End Sub

Open in new window

this code for the application ontime event should do it...

Public dTime As Date
Sub RunOnTime()
    dTime = TimeSerial(Hour(Now), 10, 0)
    Application.OnTime dTime, "doMacro"
End Sub
Sub doMacro()
    MsgBox "Macro code here"

    dTime = TimeSerial(Hour(Now) +1, 10, 0)
    Application.OnTime dTime, "doMacro"
End Sub

Open in new window

isenseAuthor Commented:
Thanks guys.  That solved it

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now