VBA - Execute Macro on Workbook Open

Question

I have 3 xlsm workbooks with a bunch of macros inside of them.. When each one is opened, the user is presented with a pop up that asks if they want to execute the Call_All macro which would run them all. If the user clicks yes, then it executes, and if no then nothing happens.

The reason I have done it this way, is because I am trying to automate the admin work as far as possible and make the process as "dummy proof" as I can for the users. So if I don't put the pop up to ask the user what they want to do and I simply call the Call_All macro in the "this workbook" file, then whenever the file is opened - even if it is to modify something (such as code), the entire macro will execute.

Now, the issue I am having is this:

I run a series of scripts and the very last script opens up the first workbook with the "Yes/No" pop up. When the user clicks on yes for this workbook, the macro's are executed perfectly. The very last macro in each workbook, opens up the next xlsm file, upon which the user is asked again if they want to execute the macros in the new workbook. However, this time it does not execute and absolutely nothing happens. The very same thing happens again when the next xlsm file is opened.

Note: If I open each xlsm file individually and click yes, then they work fine. It is only when I open the file from the previous workbook.

Here is the code to call the Call_All macro (This is in the "ThisWorkbook" File):

Private Sub Workbook_Open()

    Dim varResponse As Variant

    varResponse = MsgBox("Run Macro?", vbYesNo, "Selection")
    If varResponse <> vbYes Then

        Exit Sub

    Else

        Call_All

    End If



End Sub

The code is exactly the same in all 3 workbooks.

Try to search in all your "macros" for these line - Application.EnableEvents = False. Is it present? — Vityata
@Vityata There isn't any line with 'Application.EnableEvents = False' but there is 'Application.ScreenUpdating = False' & 'Application.DisplayAlerts = False' — Eitel Dagnin
are they followed by 'Application.ScreenUpdating = True' & 'Application.DisplayAlerts = True' ? because if no then you are turning screen updating off and never turning it back on, hence nothing appears to happen when you run the macro because the screen is not updating — Banana
@Banana I put the 'Application.ScreenUpdating = True' & 'Application.DisplayAlerts = True' at the end of the macro before the next workbook is opened and it still has not changed anything.. — Eitel Dagnin
@Banana I have even put Application.EnableEvents = True in the "ThisWorkbook" file of the workbook that is opened — Eitel Dagnin

Jerry Jerry · Accepted Answer · 2018-05-28T18:54:25

. Look at this resource (about half way down the page: https://exceloffthegrid.com/how-to-run-a-macro-from-another-workbook/

Sub CallAnotherMacro()

Application.Run "'Another Workbook.xlsm'!NameOfMacro"

End Sub

VBA - Execute Macro on Workbook Open

2 Answers