Running Macro at a specified time

[Kappy]

I read the ozgrid article that everyone keeps citing, and I just can't seem to get it to work.

For a test, I wrote this:

Sub AutoRunTest()
Application.OnTime TimeValue("14:35:00"), "MyMacro"
End Sub

Sub MyMacro()
Workbooks("AutoRun Macro Test").Sheets(1).Range("D5") = Now()
End Sub

I tried writing the time in different formats (military, standard).. Just can't seem to figure out what I'm missing here..

can anyone help? Thanks!

 

[VoG]

If you need it to keep repeating

Sub AutoRunTest()
Application.OnTime TimeValue("14:35:00"), "MyMacro"
End Sub

Sub MyMacro()
Workbooks("AutoRun Macro Test").Sheets(1).Range("D5") = Now()
Application.OnTime TimeValue("14:35:00"), "MyMacro"
End Sub

 

[Legacy 185660]

If you need it to keep repeating

Sub AutoRunTest()
Application.OnTime TimeValue("14:35:00"), "MyMacro"
End Sub

Sub MyMacro()
Workbooks("AutoRun Macro Test").Sheets(1).Range("D5") = Now()
Application.OnTime TimeValue("14:35:00"), "MyMacro"
End Sub

I'm not very good at excel, but I just had a question about this.
So wouldnt this slow down the sheet a lot because it would be repeating nonstop? Or how does it handle the code? Cause it seems to me it is just an infinite loop always running

 

[VoG]

There is no loop as such and it isn't running in the foreground. See http://www.ozgrid.com/Excel/run-macro-on-time.htm

 

[Kappy]

thanks vog,

but at this point, my concern isn't how to make it keep repeating (though that is helpful)... but rather, how to make it work at all!

to just tell excel a specific time, and have it execute a macro when that time occurs..

any ideas why my code isn't working?

 

[VoG]

Are macros enabled?

 

[Kappy]

yes.

I ran the second macro on its own just to make sure I hadn't made a mistake with that one.. and it worked fine.

but I just can't seem to get it to run automatically at the indicated time

(thanks so much for trying to help me with this)

 

[VoG]

At the risk of stating the obvious, you will have to wait until that time (your local time) is reached.

 

[Darkspartan]

As a work around, you can:
Write the macro so that it launches on workbook open
Then make a "scheduled task" that opens the workbook at a certain time.

 

[Kappy]

ha, yes... I waited for that time (and kept moving the time 5 minutes ahead when it didn't work so I could tweak the code and try again)

darkspartan, are you talking about using the windows scheduler?


in general, work around or not, its driving me nuts that I can't make this work. every google search eventually brings me back to the same code.. and it just won't work..

can you make that code work if you do it on your computer?