Workbook_Open() in Excel 2016 not firing

3
votes

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.

4
excelvba
(a) I created (using Excel 2016) a workbook with a Workbook_Open macro, saved it (macro-enabled), closed Excel, double-clicked the file in Windows Explorer, and the macro fired OK (after I selected "enable macros" of course). (b) The default behaviour now is for new workbooks to be opened within the one instance of Excel, but the windows are not "locked" together - i.e. not constrained by a single rectangular border. The fact that the windows are not constrained by the same border shouldn't (AFAIK) affect the way they operate. (And there is a workaround to open new instances of Excel.) - YowE3K
SDI (Single Document Interface) was introduced in 2013 msdn.microsoft.com/VBA/Excel-VBA/articles/… - Tim Williams
Are you opening this workbook from another workbook's VBA code? - Josh Anstead
Related: https://stackoverflow.com/a/63328366/8488913 - Cristian Buse

4 Answers

0
votes

Try encapsulating the call with a new instance of Excel. Example below:

     Sub OpenInNewExcel()

        Dim Background_Excel As Excel.Application
        Dim pathName As String
        Dim fileName As String

        Let pathName = "Enter your path here" 'include "\" at the end
        Let fileName = "Enter your file name here"

        Background_Excel.Workbooks.Open fileName:=pathName & fileName



        Background_Excel.Parent.Quit ' This is how you close the file completely using VBA otherwise the file will close and the Excel Shell will remain.


    End Sub

Also make sure that enable macros is turned on in the Options-Trust Center.

0
votes

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.

0
votes

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
-1
votes

I had this issue with one of my files as well. I managed to fix this issue by running Workbook_Open manually in the VBA editor once open and saving the file in another location. The file in the new location should have no issue with auto-running Workbook_Open. If this doesn't work, copy the original file to a new location before manually running & saving.

If the newly saved file does not run Workbook_Open, repair your version of Office.