VBA - Execute Macro on Workbook Open

[Eitel13]

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.

There isn't any line with Application.EnableEvents = False but there is Application.ScreenUpdating = False & Application.DisplayAlerts = False

I put 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.

I have even put Application.EnableEvents = True in the "ThisWorkbook" file of the workbook that is opened but nothing happened.

 

[SkipVought]

Hi,

I run a series of scripts and the very last script opens up the first workbook with the "Yes/No" pop up.

In the procedure that opens the first workbook is where you ought to open all the workbooks, rather than in a chain.

I occasionally had circunstances where a periodic update was needed in a workbook, and I used a lockout method where the Workbook_Open Event always ran but checked a flag that was set each time the update procedure ran, storing some value, like Date/Time in a VeryHidden sheet in the workbook. So no Yes/No was ever required, but the update occurred any time the workbook was opened and needed to be updated, based on the stored value flag.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]