|
Schedule a Macro to run every 5 minutes
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 |
Schedule a Macro to run every 5 minutes
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 |
Schedule a Macro to run every 5 minutes
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 |
Schedule a Macro to run every 5 minutes
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 |
Schedule a Macro to run every 5 minutes
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 |
Schedule a Macro to run every 5 minutes
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 |
| All times are GMT +1. The time now is 12:50 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com