Auto run macro on open with option to cancel

[frosty1804]

So I have been working on automating a lot of my processes and I am now trying to auto run them when my master file is opened (will auto open each day). The issue I'm having is that I need an option to abort the auto run so I can do maintenance on the files as the end of the macro saves and closes the file. I was able to write code (below) that created a message box with a Yes - run or No - abort options and if you don't click either it will auto select Yes after a few seconds (planning on running this before I get in each day); however, this code only seems to work outside of the Workbook_Open section (in it's own module that has to be manually kicked off). When the Workbook_Open macro runs, it gives the popup but won't auto dismiss like it does when it is run from a normal module. Does anyone know how to get this to work from the Workbook_Open section?


Running from Excel 2010:

Sub Workbook_Open()
      
    'Selects Main Tab
    Sheets("Master Automation Control").Select
 
    Const Title As String = "Self closing message box"
    Const Delay As Byte = 2 'this isn't exactly seconds, any value over 5 will be TOOO long
    Const wButtons As Integer = 32 + 4 ' Boutons + icon
        
    Dim Rslt As Integer
    
        Rslt = CreateObject("WScript.Shell").PopUp( _
            "Press Yes to allow auto updates to run." & vbCrLf & "Click No to cancel auto updates." & vbCrLf & _
        vbCrLf & "If no response, the updates will be made " & vbCrLf & _
        "and this workbook will be closed.", Delay, "!! Macro Auto Run !!", wButtons)
 
    Select Case Rslt
        Case 6 'Yes
            'Auto run macros
        Case 7 'No
            'Dont run any Macros - will end current macro
            Exit Sub
        Case -1 'No Response
            'Auto run macros
        End Select
    
    'EndNow:
        'For Each wb In Application.Workbooks
            'wb.Save
        'Next wb
        'Application.Quit
 
End Sub

 

[dmt32]

Hi welcome to the board.

Perhaps you have answered your own question - place code in a standard module & call it from workbook_Open event.

In standard module:

Sub AutoRun()
 'Selects Main Tab
    'Sheets("Master Automation Control").Select
 
    Const Title As String = "Self closing message box"
    Const Delay As Byte = 2 'this isn't exactly seconds, any value over 5 will be TOOO long
    Const wButtons As Integer = 32 + 4 ' Boutons + icon
        
    Dim Rslt As Integer
    
        Rslt = CreateObject("WScript.Shell").PopUp( _
            "Press Yes to allow auto updates to run." & vbCrLf & "Click No to cancel auto updates." & vbCrLf & _
        vbCrLf & "If no response, the updates will be made " & vbCrLf & _
        "and this workbook will be closed.", Delay, "!! Macro Auto Run !!", wButtons)
 
    Select Case Rslt
        Case 6 'Yes
            'Auto run macros
        Case 7 'No
            'Dont run any Macros - will end current macro
            Exit Sub
        Case -1 'No Response
            'Auto run macros
        End Select
    
    'EndNow:
        'For Each wb In Application.Workbooks
            'wb.Save
        'Next wb
        'Application.Quit
End Sub

ThisworkBook code page

Private Sub Workbook_Open()
  AutoRun
End Sub

worth noting that that code can be a little erratic in it's operation & sometimes not dismiss the message box.

Dave

 

[frosty1804]

Thanks for the quick response. I tried your suggestion and it is working about 30% of the time. I know you said that this code could be a little erratic, do you have any suggestions on an alternate way to do this or something that would be more reliable?

-David

 

[dmt32]

Hi,
have a look here:Message for a Duration

and see if any help.

Dave

 

[frosty1804]

Thanks for the reference. Found a few ideas and pieced them together to use a user form instead. Took some trial and error, but got it to do what I was needing. Thank you again for your help on this!