none
How to stop this macro with clicking a command button? RRS feed

  • Question

  • Hi

    I have a code where I copy a range and paste the values. This is done in a time interval after I click a command button. I need help on how to stop or pause this macro from running 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 have tried a couple of things to stop it.

    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

    If anyone has input of what I am doing wrong to make it work, then please let me know. Also, if you have any ideas on how to write it smoother that would alsp be appreciated.

    Thanx

    Eivind

    Wednesday, January 28, 2015 9:34 AM

All replies

  • Like this:

    Dim dtmNext As Date
    
    Sub timestamp()
        Dim N As Long
        N = WorksheetFunction.Count(Sheets("DNB").Columns(1))
        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
        dtmNext = Now + TimeValue("00:00:05")
        Application.OnTime dtmNext, "timestamp"
    End Sub
    
    Sub stoptimer()
        Application.OnTime dtmNext, "timestamp", , False
    End Sub

    Assign the stoptimer macro to the command button.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, January 28, 2015 1:41 PM