OnTime Method

[wmorrison49]

Hi everyone,
I am new to the Ontime Method, and I was hoping someone could help me through it. Does anyone have a good website for this with a tutorial? Anything that would help me with opening a workbook at a specific time and/or running a macro at a specific time. Thanks so much for your help, this site has been fantastic.

[VBA Noob]

See if these help

http://www.cpearson.com/excel/OnTime.aspx

http://msdn2.microsoft.com/en-us/lib...ffice.11).aspx

http://www.dailydoseofexcel.com/arch...ontime-method/

VBA Noob

[wmorrison49]

That helps a little bit, but I still don't know where anything goes or how to get things started. Does anyone have a simple file they could attach or something? Any help would be greatly appreciated. Thanks.

[wmorrison49]

Maybe it would help if I told people what I was doing, then you guys could correct me. In the ThisWorkbook location, I have:

Public Sub Workbook_Open()
Application.OnTime TimeValue("02:20:00"), "DoThisThing"
End Sub

Then in the same place I have a routine called DoThisThing:

Public Sub DoThisThing()
Range("A1") = "Please work"
End Sub

I am just trying to get it to do anything. So far it just sits there. Any help would be greatly appreciated.

[shg]

The argument is the date and time you want the app to run, so

Please Login or Register  to view this content.

You scheduled it to run at 2:20 AM on 01 Jan 1900.

[wmorrison49]

That's awesome shg, thank you so much. What about if I want it to run at a specific time? Say, today at 4:30 PM?

[wmorrison49]

Here's what I tried, placing this code in ThisWorkbook:

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


Public Sub DoThisThing()
Range("A1") = "Please work"
End Sub

The workbook opened just fine, but then when it tried to run the macro it said it couldn't find it. Where should I put it so it knows where to find it?

[VBA Noob]

If you had read the links I provided you would of seen these examples

This example runs my_Procedure 15 seconds from now.

Please Login or Register  to view this content.

This example runs my_Procedure at 5 P.M.

Please Login or Register  to view this content.

This example cancels the OnTime setting from the previous example.

Please Login or Register  to view this content.

VBA Noob

[shg]

Sorry, I was focused on the the time:

Please Login or Register  to view this content.

[mikerickson]

I hope these help

Please Login or Register  to view this content.

[wmorrison49]

I did read those links but it still didn't work. It is saying that is can't find my macro. Specifically it says:
"The macro "C:\Documents and Settings\ncvwxm\Desktop/Book1.xls'!DoThisThing' cannot be found

I used the lines that you two have given me, which is:

Public Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:00:05"), "DoThisThing"
End Sub


Public Sub DoThisThing()
Range("A1") = "Please work"
End Sub

I have also attached the file so you guys can see what I mean.

Thanks a million, you're the best.

[wmorrison49]

I just put the macro to be called in a Module and that did the trick. Thank you very much shg, you were extremely helpful.

[shg]

Right, functions must be in code modules ...

Glad you got it sorted out.