Scheduling Macro for Every 5 Minutes

Re: Scheduling Macro for every 5 minutes

The code below will run as soon as the workbook is opened and continue to run every minute until the workbook is closed! Just change the times to suit.

Regards,
Simon

Right click on the Excel icon next to "File" and select "View Code". In here put;

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime dTime, "Macroseconds", , False
End Sub


Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:01:00"), "Macroseconds"
End Sub

Now in any Standard Module (Insert>Module) place this;

Public dTime As Date
Sub Macroseconds()
dTime = Now + TimeValue("00:01:00")
Application.OnTime dTime, "Macroseconds"
With ActiveSheet
Range("B6").Select
ActiveCell.FormulaR1C1 = "NOW()"
Range("B6").Select
ActiveCell.FormulaR1C1 = "=NOW()"
Range("A4").Select
End With
End Sub

Re: Scheduling Macro for every 5 minutes

Yes of course, you will find that once you have pasted the code where i indicated and you try to close the workbook it will give you that error because it is trying to stop a timer that has not started. Just click end when you get the problem....re-open the workbook allow the timer to put =Now() in the required cell then close the workbook...........all should be ok! If it is a problem that you will close the workbook before the first 5 minutes has elapsed then paste this code over the sub shown below!

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error GoTo Xit
    Application.OnTime dTime, "Macroseconds", , False
Xit:      Exit Sub
End Sub

Regards,
Simon

Re: Scheduling Macro for every 5 minutes

Atheobody, please READ the rules you agreed to and the text on the New Thread page you posted from. You have ignored the use of code tags. This can lead to your posts being deleted and/or your posting rights being revoked.

See run macros at specified times

Re: Scheduling Macro for every 5 minutes

The code will initiate when opening the workbook started with this line:

Private Sub Workbook_Open() 
    Application.OnTime Now + TimeValue("00:01:00"), "Macroseconds" 
End Sub

you should not have to start it manually maybe if you add this:

Sheets("Sheet1").Select 'or you can substitute select for activate

then it should help, you can change the 1 minute in the code to be the 5 minutes you wanted and as for interrupting you im not sure try it and see.....by the way you can manually put that formula in the cell and when ever you make a change to the sheet or move cells by pressing return or enter the =Now() will update (xl2003)
Regards,
Simon

Re: Scheduling Macro for every 5 minutes

You need to pass Now + TimeValue("00:01:00") to a Variable to Cancel the OnTime Event. If not, Excel will throw and error as the time no longer matches.

Re: Scheduling Macro for Every 5 Minutes

Dave of course is dead right!, but the error handling i gave you should stop excel giving you the error message.....sloppy i know Dave but i'm still qyuite new to programming and have a head full of sawdust!

Regards,
Simon

Re: Scheduling Macro for Every 5 Minutes

Should have mentioned that if the OnTime Method is NOT cancelled it can open the Workbook at the specified time to run the macro.

Re: Scheduling Macro for Every 5 Minutes

You just have to type a square bracket then the word code then close the square bracket before your code starts and then type a square bracket then / and the word code and then close the square bracket after end sub of your code.....simple as that!

regards,
Simon

Re: Scheduling Macro for Every 5 Minutes

Didn't you follow the link about code tags in the rules? Or, on the New Thread page you posted from? On the New Thread page you posted from it even shows and example!

Quote

Wow you guys are strict.

Be honest now, you didn't read the rules did you?

Quote

FORUM RULES. READ THEM ALL, OR DONT BOTHER JOINING. WE DO ENFORCE ALL OUR RULES WITH VIGOUR. **YOU HAVE BEEN WARNED!**

These rules must be adhered to else your post might be deleted. If you cannot be bothered to take the 5mins, please don't bother posting.

Never post any code without using the Code Tags. Posting of any code without code tags makes your code hard to read and often impossible to be copied for testing. For more information about code tags, Click Here.

Quote

I'm just trying to get a question answered.

And you have been given many different ones, despite ignoring the rules you agreed to. I'm just trying to get users to adhere to the rules they agreed to.

Re: Scheduling Macro for Every 5 Minutes

I notice you chose NOT to answer my questions. Guess that in itself answers my questions. I also notice you chose NOT to thanks other members for their help (including yours truly).

Ta ta. Pick up your refund on the way out.

Re: Scheduling Macro for Every 5 Minutes

Quote

Since you are questioning my integrity without knowing anything about me

BTW, although I would think obvious, yes I am questioning your integrity BECAUSE I know nothing about you. All I know about you is you agreed to some rules in exchange for free help 24/7. Then when posting code you didn't use code tags as you agreed.

You didn't even make an attempt or read the text on the New Thread page that is boldly headed with "PLEASE READ BELOW SO YOUR POST IS NOT DELETED". Then when pulled up, rather than just "sorry wont happen again", thanks for the help so far" you come back with "Wow you guys are strict. I'm just trying to get a question answered." To make out like you didn't know "WE DO ENFORCE ALL OUR RULES WITH VIGOUR. **YOU HAVE BEEN WARNED!**"

From all this I can ONLY conclude you didn't read the rules, or chose to ignore them.