VBA to auto close msgbox or not!?!

[Dazlancs]

Hi All,

I'm trying to automate a report in Excel 2010 and have got everything working expect for one small problem. Whenever I open the report, it runs the code (which is what I want but bear with me).

I would like to have the workbook automatically open (occurring from Time Scheduler) then give me 10 seconds to press "Yes" to stop the macro and exit the sub or if this doesn't happen, continue the macro.

Any help would be great.

Thanks,
Darren

 

[AOB]

If it's a standard MsgBox, you will struggle as any code is paused until the user clicks on one of the option buttons.

Maybe design your own MsgBox (i.e. a User Form) and use the Application.OnTime method to instigate a timer which will either cancel the forthcoming operations (if/when the appropriate button is pressed) or proceed with it (if/when another appropriate button is pressed, or the timer runs down)

This thread may help...

 

[AlphaFrog]

When your workbook opens, show UserForm1 to display a Yes button (CommandButton1) and message (Label1), and count down timer (Label2)

UserForm code...

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] UserForm_Initialize()
    [COLOR=green]'Userform Message[/COLOR]
    Me.Label1.Caption = "Click 'Yes' to cancel."
    
    [COLOR=green]'Timer countdown[/COLOR]
    Me.Label2.Caption = 11
    
    [COLOR=darkblue]Call[/COLOR] CountDown
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] CommandButton1_Click()
    [COLOR=green]'Yes button (cancel countdown)[/COLOR]
    [COLOR=darkblue]Call[/COLOR] CountDown_Cancel
    Unload Me
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Put something like below in a standard code module (e.g. Module1) to control the countdown timer. Change MyMacro to the procedure you want to run if the user doesn't click Yes in 10 seconds.

[COLOR=darkblue]Public[/COLOR] NextTime [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Date[/COLOR]
    
[COLOR=darkblue]Sub[/COLOR] CountDown()
    NextTime = Now + TimeValue("00:00:01")
    UserForm1.Label2.Caption = UserForm1.Label2.Caption - 1
    [COLOR=darkblue]If[/COLOR] UserForm1.Label2.Caption > 0 [COLOR=darkblue]Then[/COLOR]
        Application.OnTime NextTime, "CountDown"
    [COLOR=darkblue]Else[/COLOR]
        Unload UserForm1
        [COLOR=darkblue]Call[/COLOR] [COLOR=#ff0000]MyMacro[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    
[COLOR=darkblue]Sub[/COLOR] CountDown_Cancel()
    Application.OnTime NextTime, "CountDown", Schedule:=[COLOR=darkblue]False[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]