Excel VBA MsgBox auto answer Yes after timer

Question

I want to schedule a task that opens an excel workbook, runs a script, and closes the workbook at a specified time each day (overnight). I've scheduled the task in Task Scheduler, and I know I can set the code to auto-run on the opening of a workbook, but I don't want the code to run EVERY time I open the workbook. Is there a way to insert a msgbox at the beginning of the code so that if there's no response to the msgbox within 60 seconds, it will automatically run the code. Here's what I'm envisioning, but can't figure out how to format the "Case Timeout" portion of it:

Sub Auto_Run()
MsgBox "Would you like to run reports now?", vbYesNo
Case vbYes
'Insert code here
Case Timeout
'paste the same code as for vbYes
Case vbNo
End Sub

Answer 1

Just have it as a regular sub and write a batch script that you schedule. I used to do this all the time but can't find my old scripts. Found this and I know it's similar to my old ones.

Dim xlApp
Dim xlWkb
Set xlApp = CreateObject("excel.application")
Set xlWkb = xlApp.Workbooks.Open("PATH TO YOUR FILE")
xlApp.Visible = True
xlWkb.RunAutoMacros 1 'enables macros to be run on open
xlApp.Run ("YOUR PROCEDURE")
xlApp.Workbooks("YOUR WORKBOOK NAME").Save 'Save the workbook
xlApp.Quit 'quits excel

Save as .vbs and schedule it to run rather than the workbook . This way you can open and edit your script any time you want without it auto running.

Answer 2

You could try a splash screen approach.

Create a new worksheet, say StartUp and make it look something like this:

In this picture I have hidden gridlines, etc., changed the background and the font, then included two rectangles linked to subs.

The way the subs work:

1) In a standard code module (which contains the Report sub that you want to (sometimes) run), I have a variable declared at the top of the module as:

Public TimeOut As Boolean

2) In the code module for my startup sheet I have these two subs (linked to the corresponding shapes):

Sub RunReport()
    Sheets("StartUp").Visible = xlSheetHidden
    TimeOut = False
    Report 'sub to launch
End Sub

Sub UseWorkbook()
    Sheets("StartUp").Visible = xlSheetHidden
    TimeOut = False
End Sub

3) In the module for ThisWorkbook I have:

Private Sub Workbook_Open()
    Dim start As Double

    TimeOut = True
    Sheets("StartUp").Visible = xlSheetVisible
    Sheets("StartUp").Activate
    start = Timer
    Do While Timer < start + 60
        DoEvents
        If TimeOut = False Then Exit Sub
    Loop
    Sheets("StartUp").Visible = xlSheetHidden
    Report
End Sub

When you open the workbook the Open event is fired, displaying the splash screen. This Workbook_Open sub then enters a 60-second loop, monitoring the variable TimeOut. If it ever becomes false (by a splash-screen button being pressed) -- the sub ends (with the event handlers for the shapes hiding the splash screen). Otherwise after 60 seconds the splash screen is automatically hidden and the Report sub automatically run.

Final Remark: the DoEvents is needed to monitor the TimeOut variable and allow for the other event handlers to fire, so you wouldn't want to simply sleep for 60 seconds. Nevertheless, you can still use the excellent suggestion of @teepee by introducing 1 second sleeps in the middle of the Do-While loop. It is probably enough to check TimeOut once a second.