MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Is there a way to run time dependant macros?

Posted by Igor on January 27, 2002 8:07 AM

I have a set of macros, that I am looking to run at different times of the day. Usually I have to start them up myself. Is there a way that I can get them to run at a specific time in the day?

Many Thanks for any suggestions

Posted by DK on January 27, 2002 9:55 AM


You can use OnTime e.g.

In the workbook code module put this:-

Private Sub Workbook_Open()
'Run YourSub at 6pm
Application.OnTime TimeValue("18:00:00"), "YourSub"
End Sub

Then in a normal module you'd have YourSub...

Sub YourSub()
MsgBox "Hello"
End Sub

Hope this helps,

Posted by Igor on January 27, 2002 10:18 PM


Thanks for the advice. I tried running your small macros, and they seem to work. Only problem is, they aren't time dependant. When I run the macro, it just refers the message box and pops up with the message irregardless of what the time is. Am I doing something wrong?

Ultimately I would like this to occur :

Excel loaded.

at 5pm, a macro is set off by the time, and runs without having to be run manually.

Thanks again for all the help

Posted by DK on January 28, 2002 5:20 AM


Did you put the Workbook_Open procedure in the Workbook's code module? If so, this will run automatically when you open the workbook. It will set the macro YourSub off at the time specified (in this case 6pm).

You can only run macros at specified times if the workbook containing the macro(s) is open.

If you can't get the example to work, send me an email at the address above.