How can this code be modified in order to stop a macro from running every 5 minutes? Public dTime As Date Sub StopTimer() On Error Resume Next dTime = Now + TimeValue("00:05:00") Application.OnTime dTime, "Macro2", False End Sub Any help is greatly appreciated.
Not sure what the problem is... Sub StopTimer() is something you've written. Where is it being called from? In the caller do something like... If some_condition then result = Application.Run macroname end if maybe some_condition could be an external file containing a time offset, or 0. Even easier, a cell on some worksheet! If it's 0 or an invalid time offset, don't run the macro (or do whatever is needed to stop it if it's running). Now get rid of Application.OnTime in StopTimer(), which as written is definately misnamed. Good luck
This morn I was working in xlXP(sp3) and l looked up Application.Ontime. According to the last example in the docs, you can specify the previous time value (exactly as used to start the macro) to cancel it. So instead of my previous extemporaneous suggestion, based on docs you could use a Static or a Public to stash the time last used to start the macro. Then use that stashed value to stop it. You will still need some conditional to determine whether or not to restart the macro or stop it. Something UNTESTED like... Public vntLastStartTime As Variant Sub StopTimer() If JaneStopThisCrazyThing Application.OnTime vntLastStartTime , "Macro2", False vntLastStartTime = 0 Else dTime = Now + TimeValue("00:05:00") Application.OnTime dTime, "Macro2", False vntLastStartTime = dtime End if End Sub Using this concept, it might be useful to add an argument to the Sub that would be the conditional. Good luck