none
Excel Macro Auto_Open() only opens first time RRS feed

  • Question

  • Dear all,

    I developed a Excel Workbook that automates some task for tests we run in our company. Colleagues can create templates for tests using this workbook. Upon opening, it runs the following macro (in a module):

    Public Sub Auto_Open()
    
        On Error Resume Next
        
        Application.Run ("CreateCollectDataBTN")
        Application.Run ("CreateSaveFileBTN")
        Application.Run ("ReloadDir")
        
        On Error GoTo 0
    
    End Sub

    Upon closing, it runs a Auto_Close() to close some things and save the file correctly.

    This worked for everyone. Now, my company migrated to Office 365 storage, and for some reason, the Auto_Open script only runs the first time someone opens the script. So, I open a file, it runs the macros it needs to run. Then, when I open the template again, it does not run the Auto_Open macro but it runs the Auto_Close macro instead. If I then rename the file to File.v2.xlsm it runs the correct macros again. However, when closing it and reopening the file, it does not work anymore unless I rename the file. This behavior also occurs on laptops of colleagues.

    Is there a setting I need to change in order to prevent this behavior?

    Thank you for your help,

    Hans


    Monday, January 2, 2017 2:48 PM

All replies

  • Auto_Open is old technology. I thought that it was still working but maybe it is giving problems with later versions of Excel. The replacement (Current technology) is to use event code.

    Remove the Auto_Open sub that you have and then in the VBA editor, double click ThisWorkbook in the Project Explorer (Left column in the editor). 

    Then insert the following code. Note that I have changed the sub name and you must keep that name for the code to run when the workbook is opened.

    Private Sub Workbook_Open()

        On Error Resume Next
       
        Application.Run ("CreateCollectDataBTN")
        Application.Run ("CreateSaveFileBTN")
        Application.Run ("ReloadDir")
       
        On Error GoTo 0

    End Sub


    Regards, OssieMac

    Wednesday, January 4, 2017 3:40 AM