Edit: Maybe someone also has an example workbook of something like this already!
I wanted to create some type of program (in VBA) to pop up when its time for a user to take a break or lunch, or meeting etc.
This would require the user to enter a series of time.
My thought was to use a userform that allows the user to start with essentially inputting their start time, break1, lunch, break2, end of shift time
I just thought of a single time box, with insert before/insert after button and add these times to a listbox =p
I was thinking the best method would be some form of use with Application.OnTime to call a procedure when the time is reached, the OnTime procedure would then call the next OnTime method to run the next time in the loop
Ive not created anything like this before, and before starting a big endeavor I am looking for input on possible other methods, or complications I might run into. Is there maybe an API call I should use that would work better than this? Is the Application.Ontime going to cause interuption in the rest of the workbook (since a lot of other VBA code will be run besides the schedule program)
If Google = NoHelp Then PostHere = True
Last edited by Josh Hazel; 02-17-2010 at 07:37 PM.
The Application.OnTime is an easy and effective way to schedule tasks within Excel. The only problem you may run into, if your workbook runs a lot of code frequently is that the OnTime procedure will wait until the there is a break in code execution before it kicks off the designated procedure. So if the OnTime scheduled task fires while another macro is running, it will wait until that macro completes before calling the scheduled procedure. Does that make sense?
Also, the OnTime event gets cancelled if the workbook is closed. So if there's an issue of some sort and the workbook must be closed and reopened, you'll lose your scheduled tasks.
Here's an example of using a CommandButton to schedule an event to run 15 seconds later:
'Code placed in UserForm:
Private Sub CommandButton1_Click()
Application.OnTime Now + TimeValue("00:00:15"), "SayHello"
'Code placed in Module:
Public Sub SayHello()
Im wondering, can I run multiple application.ontimes?
We are talking about scheduling start time of shift, start time of break 1, end time of break 1, etc ...
Can I run application.ontime multiple times and will run the procedure requested for each of those times, or do I need to have the procedure that is run trigger a new application.ontime for the next time in the schedule?
Okay, and I tested it out, seems to work.... next step is to see how to update (ie stop the .ontime method) and remove all scheduled tasks - considering someone might update their timesheet... im sure that will prol be ez enuf to find that syntax online, whenever i add/delete/update a time i think ill have to remove all times and launch new times