Hello,

Can anyone tell me (in real simple terms) how to run a macro every 5 minutes.

I'd like to hit my hot button "Ctr-Q" and then have it run every 5 minutes
for ever (or until I close the spreadsheet).

I already have the macro done, I just need to add the part I'm asking. I
would ask that you "idiot proof" it for me as I've tried to read Chip
Pearson's page but it was too difficult for me to follow.
Thanks,
atheobody

look in the vba help for ONTIME

--
Don Guillett
SalesAid Software

"Atheobody" wrote in message
...
Hello,

Can anyone tell me (in real simple terms) how to run a macro every 5
minutes.

I'd like to hit my hot button "Ctr-Q" and then have it run every 5 minutes
for ever (or until I close the spreadsheet).

I already have the macro done, I just need to add the part I'm asking. I
would ask that you "idiot proof" it for me as I've tried to read Chip
Pearson's page but it was too difficult for me to follow.
Thanks,
atheobody

Additional examples

http://www.cpearson.com/excel/ontime.htm

"Atheobody" wrote:

Hello,

Can anyone tell me (in real simple terms) how to run a macro every 5 minutes.

I'd like to hit my hot button "Ctr-Q" and then have it run every 5 minutes
for ever (or until I close the spreadsheet).

I already have the macro done, I just need to add the part I'm asking. I
would ask that you "idiot proof" it for me as I've tried to read Chip
Pearson's page but it was too difficult for me to follow.
Thanks,
atheobody

Hello,

I read that page but it doesn't make sense to me. I just don't undestand.
Isn't there an example you could copy and paste for me that I could copy and
paste into my macro?

"JMB" wrote:

Additional examples

http://www.cpearson.com/excel/ontime.htm

"Atheobody" wrote:

Hello,

Can anyone tell me (in real simple terms) how to run a macro every 5 minutes.

I'd like to hit my hot button "Ctr-Q" and then have it run every 5 minutes
for ever (or until I close the spreadsheet).

I already have the macro done, I just need to add the part I'm asking. I
would ask that you "idiot proof" it for me as I've tried to read Chip
Pearson's page but it was too difficult for me to follow.
Thanks,
atheobody

Thanks! Where speficially is the VBA help for ONTIME. You mean in Excel
help on my Excel program? Or on this forum? I'm still very confused.

"Don Guillett" wrote:

look in the vba help for ONTIME

--
Don Guillett
SalesAid Software

"Atheobody" wrote in message
...
Hello,

Can anyone tell me (in real simple terms) how to run a macro every 5
minutes.

I'd like to hit my hot button "Ctr-Q" and then have it run every 5 minutes
for ever (or until I close the spreadsheet).

I already have the macro done, I just need to add the part I'm asking. I
would ask that you "idiot proof" it for me as I've tried to read Chip
Pearson's page but it was too difficult for me to follow.
Thanks,
atheobody

alt f11f1indextype in ONTIME

--
Don Guillett
SalesAid Software

"Atheobody" wrote in message
...
Thanks! Where speficially is the VBA help for ONTIME. You mean in Excel
help on my Excel program? Or on this forum? I'm still very confused.

"Don Guillett" wrote:

look in the vba help for ONTIME

--
Don Guillett
SalesAid Software

"Atheobody" wrote in message
...
Hello,

Can anyone tell me (in real simple terms) how to run a macro every 5
minutes.

I'd like to hit my hot button "Ctr-Q" and then have it run every 5
minutes
for ever (or until I close the spreadsheet).

I already have the macro done, I just need to add the part I'm asking.
I
would ask that you "idiot proof" it for me as I've tried to read Chip
Pearson's page but it was too difficult for me to follow.
Thanks,
atheobody

Sorry - I must have read over the part where you mentioned you had already
been to Chip's website. I can't give any examples better than what Chip has
already done, so I can only repost his code and offer a little explanation.

These are the variable declarations that go at the top of your vba module.
You will need to change "The_Sub" to the name of your macro that you want to
run every 5 minutes.

Public RunWhen As Double
Public Const cRunIntervalSeconds = 300 ' five minutes
Public Const cRunWhat = "The_Sub" '<<<<< CHANGE


This procedure is called from "The_Sub". This instructs VBA to run
"The_Sub" again in 5 minutes.

Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedu=cRunWhat, _
schedule:=True
End Sub

This is the macro you want to run every 5 minutes and should be linked to
your hotkey, Ctrl-q. Since your macro is already done, you just need to add
StartTimer to the end of it to get it to run again in 5 minutes.

Sub The_Sub()
'
' your code here
'
StartTimer '<<< Put at the end of your macro
End Sub

As Chip pointed out, even after you close the workbook, Excel will open it
and run your macro in 5 minutes unless you stop the timer. This is a macro
you could assign to a button to stop the timer.

Sub StopTimer()
On Error Resume Next
Application.OnTime earliesttime:=RunWhen, _
procedu=cRunWhat, schedule:=False
End Sub


Alternatively, you could have excel stop the timer process automatically by
putting the stop timer procedures in a workbook close event handler. Instead
of putting it in a regular vba code module, you have to put it in the
ThisWorkbook module (in the vba editor, you have to have the Project Explorer
open (Ctrl-R) to see the various modules of your project.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.OnTime earliesttime:=RunWhen, _
procedu=cRunWhat, schedule:=False
End Sub


If you need more info on how to navigate the vba editor, you should visit
David McRitchies web site. He has a few tutorials that will likely help.
Here is one on getting started w/macros
http://www.mvps.org/dmcritchie/excel/getstarted.htm



"Atheobody" wrote:

Hello,

I read that page but it doesn't make sense to me. I just don't undestand.
Isn't there an example you could copy and paste for me that I could copy and
paste into my macro?

"JMB" wrote:

Additional examples

http://www.cpearson.com/excel/ontime.htm

"Atheobody" wrote:

Hello,

Can anyone tell me (in real simple terms) how to run a macro every 5 minutes.

I'd like to hit my hot button "Ctr-Q" and then have it run every 5 minutes
for ever (or until I close the spreadsheet).

I already have the macro done, I just need to add the part I'm asking. I
would ask that you "idiot proof" it for me as I've tried to read Chip
Pearson's page but it was too difficult for me to follow.
Thanks,
atheobody