Auto Schedule multiple OnTime procedures from one manually activated procedure?

[StuLythgoe]

I am currently trying to automate a large number of auto OnTime procedures which currently need to be activated by assigning each OnTime procedure to a button or by going into the VBE and manually activating them from there. Is it possible to write a single procedure and assign it to a button in the worksheet to activate say 10 OnTime procedures so they will run automatically throughout the day??

 

[Andrew Poulsom]

Why do you need multiple OnTime schedules? Can't you call all the procedures from a single scheduled procedure?

 

[StuLythgoe]

Because of the way we work and having three rolling shifts 24-7 one report may be run at 07:30, 10:30 & 13:30, then another report may only be run once at 10:00, another at 12:00 and they are all different reports with different VBA procedures associated with them.

 

[StuLythgoe]

Just to apologise, I think in my initial post there wasn't much clarity as to why I would want to run multiple procedures at different times, so my apologies. We use Cognos Impromptu to query our WMS database. The customer specifies reports that they want running at different times as they assess stock levels or the amount of stock in quarantine status or whatever so the times we run our reports are defined by the customers wants. I have written the existing procedures so that they run the relevant impromptu report, feed that into Excel, apply a host of formulas, import other sheets, different formatting, etc and some of the reports can take up to 15 minutes to run, so as you can imagine, with around 50 reports per shift to run, we can't afford to have Excel tied up all that time while we wait for each report to be generated by Cognos, fed into Excel, etc. So I was hoping it would be possible to write a number of seperate OnTime procedures to fulfill the time requirements for each report and then activate the OnTimes for each one by assigning a procedure to one button which the individual on shift to click when they come in and each report would then be prepared at the relevant time without consuming too much CPU processing time on the server. Hope that is a little clearer.

 

[Andrew Poulsom]

You can schedule more than one procedure using OnTime. Example:

Sub Test()
    Application.OnTime Now + TimeValue("00:00:01"), "Proc1"
    Application.OnTime Now + TimeValue("00:00:02"), "Proc2"
End Sub
 
Sub Proc1()
    MsgBox "Proc1"
End Sub
 
Sub Proc2()
    MsgBox "Proc2"
End Sub

 

[StuLythgoe]

I must apologise for being an idiot. I didn't read your initial response properly and was on a completely different wavelength. After rereading your reply I realised it made perfect sense. Thanks for clearing my head on that issue, that is perfect. Thanks