Create a Macro Button to stop current Macro that is running

[powellpowers]

Hi all,

I have a macro that loops every 20 minutes as follow:

Sub Timer()


Application.Run "'Macro1"
Application.OnTime Now + TimeValue("00:20:00"), "Timer"

End Sub


Is there any way I can have a script to stop this from looping that I can assign to a clickable button?

Thank you!

 

[VBA Geek]

store the time at which the macro will be run next into a public variable


then you can have a routine like

Application.OnTime ScheduledTime, "Timer", , [B]False[/B]

https://msdn.microsoft.com/en-us/library/office/ff196165.aspx

 

[powellpowers]

That is a very good advice.

Do you know how I could do it?

From my understanding you are suggesting that this line: Application.OnTime Now + TimeValue("00:20:00"), "Timer"

I should make Now + Timevalue("00:20:00") as a variable based on a cell in my spreadsheet, and subsequently I can change the cell to "false".

Is that right? I'm a total noob in this.