the procedure names are constant, so create two public variables in a module to track the times you set for the Annoy_* functions (since those will vary). You can cancel a scheduled OnTime procedure only if you know both (1) the time the procedure was scheduled for and (2) the procedure that was to be called. Here's what i came up with after googling how the OnTime function works: So what I'm trying to do, is make the auto_open Application.OnTime function to only work when the worksheet "2017 Log File" is active (If "2017 Log File" Is Nothing Then Cancel Application.OnTime) It then OPENS the file again, and goes through the point awarding and emailing, which then goes into complete chaos because it opens the file again, which creates another timer etc. So if you close it before 45 minutes, the timer keeps ticking.
if you have another Excel spreadsheet running at the same time, the timer keeps running (function is working off of application not workbook). Sub Annoy_Close() Annoy_Email ThisWorkbook.Save
ThisWorkbook.Worksheets("2017 Log File").OnEntry = "folder_change"Īpplication.OnTime Now + TimeValue("00:15:00"), "Annoy_Run"Īpplication.OnTime Now + TimeValue("00:45:00"), "Annoy_Close" Sub auto_open() ' Run the macro folder_change any time a entry is made in an observed cell
At 45 minutes a code runs, saves the file, and closes it, and then sends me an email awarding the person an "awful archiver point" which we run in a leaderboard.
I made a macro that shoots out a Userform every 15 minutes reminding people to close it. There's a problem with people opening it and keeping it open locking it out for everyone else who needs to edit it. So I've got this sheet that is on a shared server for my company.