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!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

CTRL + BREAK from second macro button

Status
Not open for further replies.

remeng

Technical User
Jul 27, 2006
509
1
0
16
US
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
 
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
 
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]
 
>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.
 
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top