title: How to stop a macro by clicking a command button?
body: <p>I have a code to copy a range and paste the values.</p>
  
  <p>This is done in a time interval after I click a command button.</p>
  
  <p>How do I stop or pause this macro by clicking a button?</p>
  
  <p>Here is the main code:</p>
  
  <pre><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
  </code></pre>
  
  <p>I tried a couple of things.</p>
  
  <blockquote>
    <ol>
    <li>by BREAK function</li>
    </ol>
  </blockquote>
  
  <pre><code>Sub PauseMacro()
      Application.SendKeys "^{BREAK}"
  End Sub
  </code></pre>
  
  <blockquote>
    <p>2.</p>
  </blockquote>
  
  <pre><code>Public StopMacro as Boolean
  Sub SetStopMacro()
      StopMacro = True
  End Sub
  </code></pre>
  
  <p>and put it in the code as this:</p>
  
  <pre><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:10"), "timestamp"
  
      DoEvents
  
      If StopMacro = True Then Exit Sub
  
  End Sub
  </code></pre>