Workbook_Open() in Excel 2016 not firing

Question

Excel 2016 (or 365) does not seem to fire the Workbook_Open() sub reliably or more precisely, not at all! The simple event sub

Private Sub Workbook_Open()
    MsgBox "Work book is open"
End Sub

does not seem to work. However, if a workbook is already open and then the workbook containing the above Sub is then opened, it does run as expected. I notice that unlike Excel 2010, 2016 (365) opens each workbook in its own window, not a workbook window in the Excel application window. Is this a bug in 2016 and is there a workaround?

I have produced a work around for my own applications and that is call the activation of a worksheet and call my initialization routines from there. But a bit "rough" and it would be good to have the Workbook_Open() sub working correctly.

It is a simple single Sub in the ThisWorkbook module. Macros are enabled. In Excel 2010 it works perfectly, as do two other macros in other workbooks for which I have written macros. It is just this one thing with Excel 2016. Given that the Workbook_Open() sub is the gateway to a workbook it seems a little strange that we have to go to a workaround to make it function.

user_2181188 · Answer

You have to add the file/folder location of your workbook as a "Trusted Location".

You can see more info about that in Designate trusted locations for files in Office 2016.

user_10179421 · Answer

I have same problem then I found solution after google it: https://www.myonlinetraininghub.com/excel-forum/vba-macros/excel-2016-workbook_open-event-doesnt-trigger

Then I also use "Private Sub Workbook_Open()" and "Public Sub Auto_Open()" open in excel 2016 that work fine:

Private Sub Workbook_Open()
    CustomStartUp
End Sub

Public Sub Auto_Open()
    CustomStartUp
End Sub


Private Sub CustomStartUp()
    MsgBox "Work book is open"
End Sub

user_17330473 · Answer

I've had this problem (I'm using Microsoft 365), and I found this thread.

It happens for me sometimes when I have another workbook already open, then, on trying to open my macro-enabled workbook, before any sheet is displayed I get the Microsoft warning message about macros. Then, although I click its 'Enable' button, the Workbook opens, macros do get enabled, but Workbook_Open doesn't run.

I've never known the problem to occur if no other workbook is open. (Of course, the user might still get the yellow-backed messages at the top of the workbook, asking them to click the Enable Editing and/or Enable Macros buttons.)

Note that my Workbook_Open just calls another 'workbook-opening' sub in a module to do all the opening processing.

My solution: When my workbook-opening sub is called, it sets a global variable to True to indicate it has run.

I've made it obvious to the user that the problem has occurred, by means of a 'Welcome' sheet with all its cells locked, so the user can do nothing; at this point all other sheets are very hidden. The workbook-opening sub, when it runs, deactivates this sheet and makes it very hidden, so the user never normally sees it, and makes the other sheets visible. But if this screen remains, it instructs the user to select the other workbook, then select this one again. My Workbook_Activate code then runs, and because the global variable isn't True, it calls the workbook-opening sub. If this global variable is True, it does nothing.

To make this work, the Workbook_Close sub makes the other sheets very hidden and the Welcome sheet visible, ready for the next time the Workbook is opened.

Hey presto, the problem is solved.

The Welcome sheet actually has a dual purpose, in that if either of the yellow-backed warning messages are displayed, it will remain and force the user, with suitable instructions, to click Enable Editing and/or Enable macros. If the users aren't au fait with macro-enabled Excel, they will just ignore these and try to carry on regardless.

All this is much easier to implement than to explain. I hope it's clear.

And I hope this might be of help to someone.

Workbook_Open() in Excel 2016 not firing

5 Answers