How to stop a macro by clicking a command button?

Question

I have a code to copy a range and paste the values.

This is done in a time interval after I click a command button.

How do I stop or pause this macro by clicking a button?

Here is the main code:

Sub timestamp()
'
' timestamp Macro
'   
    N = WorksheetFunction.Count(Sheets("DNB").Columns(1))   

    dnbspread = Sheets("DNB").Range("G5:G30")

    Sheets("DNB").Cells(N + 34, 1) = Date
    Sheets("DNB").Cells(N + 34, 2) = Time
    Sheets("DNB").Range("G5:G30").Copy

    Sheets("DNB").Cells(N + 34, 3).PasteSpecial Transpose:=True, Paste:=xlPasteValues

    Application.OnTime Now + TimeValue("00:00:05"), "timestamp"

End Sub

I tried a couple of things.

1. by BREAK function

Sub PauseMacro()
    Application.SendKeys "^{BREAK}"
End Sub

2.

Public StopMacro as Boolean
Sub SetStopMacro()
    StopMacro = True
End Sub

and put it in the code as this:

Sub timestamp()
'

' timestamp Macro

'
    N = WorksheetFunction.Count(Sheets("DNB").Columns(1))

    dnbspread = Sheets("DNB").Range("G5:G30")

    Sheets("DNB").Cells(N + 34, 1) = Date

    Sheets("DNB").Cells(N + 34, 2) = Time

    Sheets("DNB").Range("G5:G30").Copy

    Sheets("DNB").Cells(N + 34, 3).PasteSpecial Transpose:=True, Paste:=xlPasteValues

    Application.OnTime Now + TimeValue("00:00:10"), "timestamp"

    DoEvents

    If StopMacro = True Then Exit Sub

End Sub

Answer 1

Public StopMacro As Boolean

Sub SetStopMacro()
    StopMacro = True
End Sub

Sub timestamp()
    '
    ' timestamp Macro
    '
    ' code here
    '
    ' Put If-Then before Application.OnTime call. 
    ' So prevent next call of 'timestamp()' if StopMacro equals to true
    If Not StopMacro Then
        Application.OnTime Now + TimeValue("00:00:15"), "timestamp"
    End If
End Sub

Answer 2

You can declare a global variable inside the module where the macro is running from, and then, when the command button is clicked, assign a value to that variable you just declared. Then, create an if statement, where the code exits if the variable equals to that value.

Private Sub CommandButton2_Click()   
btncancel = 1
EMSetup.hide
Exit Sub
End Sub

For the command button.

Global btncancel As Integer
If btncancel = 1 Then
  Exit Sub
End If

Hope this helps!