CTRL + BREAK from second macro button

remeng · Jul 8, 2024

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:

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 · Jul 8, 2024

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 [smile]

Herman
Say no to macros

strongm · Jul 8, 2024

Code:

[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 · Jul 8, 2024

>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 · Jul 8, 2024

Hi Strongm,

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

Code:

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

strongm · Jul 8, 2024

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

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

CTRL + BREAK from second macro button

remeng

Technical User

hermanlaksko

Programmer

strongm

MIS

strongm

MIS

remeng

Technical User

strongm

MIS

Similar threads

Part and Inventory Search

Sponsor