CTRL + BREAK from second macro button

[remeng]

Hi,

I have a macro timer that runs every set period. This macro works just fine, but at some point, a user might need to stop the macro timer from running its next cycle.

The period can be as short as every 5 seconds or as long as 24 hours.

I've tried several of the error(18) capture methods, but it leaves the macro in break mode preventing the original macro from being executed / restarted again without manual intervention.

It looks like I can also run a second macro when the sub macro_timer() is running. The goal is to stop the loop when the person presses the execute button on the kill macro.



current timer code:

Sub macro_timer()

On Error GoTo error_msg

refresh_hours = ThisWorkbook.Sheets("Control Panel").refresh_hours_textbox.Value

refresh_minutes = ThisWorkbook.Sheets("Control Panel").refresh_minutes_textbox.Value

refresh_seconds = ThisWorkbook.Sheets("Control Panel").refresh_seconds_textbox.Value

interval_time = refresh_hours & ":" & refresh_minutes & ":" & refresh_seconds



'Tells Excel when to next run the macro.

Application.OnTime Now + TimeValue(interval_time), "thisworkbook.pdf_print_macro"

GoTo continue_macro

error_msg:

    MsgBox "An error has occured while setting the timer interval." & Chr(10) & Chr(10) & "Restart the PDF Print maacro to continue." & Chr(10) & Chr(10) & "If the problem continues, contact your Macro Support Team.", vbCritical + vbOKOnly, "CRITICAL ERROR WHILE RUNNING THE TIMER"
    
    End

continue_macro:

End Sub

Thanks for the help,

Mike

 

[hermanlaksko]

Perhaps I am overlooking something - but your "continue_macro:" does nothing - so either it should not be there or it should call "macro_timer" or something else

Herman
Say no to macros

 

[strongm]

[COLOR=blue]Option Explicit
Public printticktime As Date

Sub macro_timer()
    printticktime = DateAdd("s", 1, Now)
    Application.OnTime printticktime, "thisworkbook.pdf_print_macro"
End Sub

Public Sub pdf_print_macro()
    Debug.Print "tick " & printticktime
    printticktime = DateAdd("s", 1, Now)
    Application.OnTime printticktime, "thisworkbook.pdf_print_macro"
End Sub

Public Sub stoptimer()
    Application.OnTime printticktime, "thisworkbook.pdf_print_macro", , False
End Sub[/color]

 

[strongm]

>but your "continue_macro:" does nothing

Not true. It skips the error handling. More typically we might put Exit Sub there, BUT some people adhere to the one entry point/one exit point rule for procedures, which Exit Sub breaks.

 

[remeng]

Hi Strongm,

Does the second button call stoptimer() which triggers the cancel operation?

Public Sub stoptimer()
    Application.OnTime printticktime, "thisworkbook.pdf_print_macro", , False
End Sub

 

[strongm]

It is edample code, not a full solution.

>The goal is to stop the loop when the person presses the execute button on the kill macro.

So, sure, put the call to stoptimer in the button click event. And yes, it cancels the matching timer event