Running Macros When a Workbook is Opened

There are two ways to run a macro when a workbook is opened; the Workbook_Open event and an Auto_Open macro.

The Workbook_Open event resides in the ThisWorkbook module. The Auto_Open macro belongs in a standard module. If you have both, the event procedure will fire before the Auto_Open procedure.

Other than their location, the primary difference between the two is what happens when you open a workbook via code. The event will still fire, the Auto_Open macro will not. VBA provides a RunAutoMacros method of the workbook object that allows you to run the Auto_Open code when a workbook is opened through VBA. Here’s an example that demonstrates how to use that method.

Sub OpenWorkbookViaCode()

    Dim wb As Workbook
    
    ‘event code will fire on this line
    Set wb = Workbooks.Open(sPath & “WorkbookOpen.xls”)
    
    ‘You need this line to run Auto_Open
    wb.RunAutoMacros xlAutoOpen
    
End Sub

These two examples show the syntax for the event procedure (which goes in ThisWorkbook) and the Auto_Open (which goes in a standard module), but they don’t anything except show a message box.

Private Sub Workbook_Open()

    MsgBox “Workbook Open Event”
    
End Sub

Sub Auto_Open()

    MsgBox “AutoOpen”
    
End Sub

You don’t need to put your code directly in the events. You create whatever code you need in a standard module and call that code from the event. The same goes for the Auto_Open. If you ever wanted to switch from one method to the other, you might be happy to do it that way. I generally do it when the code is sufficiently complex, but for simply Open events, I don’t.

Now you probably want to know which one to choose. I don’t know, leave me a comment on which you use and why. I use the Workbook_Open event almost exclusively. The Auto_Open is the “old” way of doing it. I see plenty of very experienced coders using Auto_Open, though, so there may be some advantage. If there is, I don’t know it.

Posted in Uncategorized

13 thoughts on “Running Macros When a Workbook is Opened

  1. Hi,

    I am trying to use the NETWORKDAYS function in Excel and have encountered the following problem.
    The function works fine with a given start and end date. But when I copy and paste it to the next row, I get the same answer as the previous row even though the start and end dates are different. I have 10,000 rows to perform the same NETWORKDAYS function.

    Any ideas.

    Much appreciated.

    Krishnan.

  2. Krishnan, make sure that you don’t have absolute references in your formula. If there are any dollar signs, remove them from the top formula before copying it down.

  3. Back to the Auto_Open vs. Workbook_Open debate, when I was first learning this newfangled VBA, some old timers claimed that there were issues with Workbook_Open not always working correctly. I didn’t know what it was back then, and I don’t know what it might have been, looking back now. I always use Workbook_Open, never Auto_Open, and I don’t think I’ve ever had any difficulties.

    – Jon

  4. Hi,

    Once the Workbook_Open macro has been put into a workbook is it possible to open that workbook without the macro running.

    Any ideas?

    Thank you very much.

    Richard

  5. Hi,

    I am trying to bulk add the same Workbook_ Open procedure to about 120 different Workbooks. I cannot do it though. I was trying to do so using

    Set VBP = ActiveWorkbook.VBProject

    With VBP.VBComponents
    .Import Filename

    RMG

    ‘where filename contains a Sub Workbook_Open()
    procedure

    However, the .Import Filename diesn’t work. I can bulk add this as a module for each individual spreadsheet and import the file to a module but this doesn’t work – i need the code to work when the file is opened. Could I adapt your auto_run() code above?

  6. I just wrote an Auto_Open macro which also clears all filters upon opening the workbook. I tested the macro and it worked fine. When I advised my fellow associates of this macro and to enable macros when opening this file, it did not work for them. I am trying to figure out why. I’m wondering if it is because none of them have a macros toolbar on their machines? Can anyone think of any other possible reason? (It still works fine on my machine, and everyone accesses this file thru the network. I have not saved it as a shared workbook, could this also pose a problem?

  7. I have seen Excel 2000 9.0.3821.SR-1 have problems with the workbook_open event or other events such as activate not firing. Changing the security settings (Tools-Macro-Security) to Low did not help. The only thing that worked was reboot the computer. Then the events do happen again.
    I suspect it may have something to do with VBA running into an error. I have not been able to reproduce the problem.

  8. If you use events heavily in alot of workbooks, then it might be useful to have an Auto_Open to run the Workbook_Open actions if events have been disabled (perhaps from an uncaught error in a segment of code which had disabled events).

  9. I also have ran into an issue with Workbook_Open not working properly when opening up the workbook with a such code. Generally it works fine, but there has been cases when the “EnableEvents” has been set to false for some strange reason (and no, this was not via code as I rarely ever use the property in code, though if debugging or it errored out and didn’t get past such code to re-enable the events by setting this property back to true, then this would be one such case where it would cause the Open Event on the Workbook object to NOT work).

    As a result of this event not working properly in those unknown cases, I almost always use the “Private Sub Auto_Open()” within a standard module. If we still need to have a such macro to work after opening up the workbook via code, I could then use the “Application.Run” method to run such code. Of course, the Auto_Open procedure would only call on another procedure that is set to “Public”, so as the Application.Run method can see the procedure that it needs to run, given the “Auto_Open” procedure is set to Private as that is the only way I been able to get it to work properly.

  10. Hi,

    I’m creating an excel template that contains a macro which will show my userform when I run it. The problem is, the moment i save the workbook as a Template, both the macro and the userform will not be saved in the template.

    I would also like to know how I may save a template on a shared drive and make it visible to all users.

    Thank you.

  11. Well, this is the first time I’ve ever heard of it! I was scratching my head looking at some code I’d inherited that had a Sub called Auto_Open() in it, and I thought “Hey, the person that coded this up forgot to call it via a Workbook_Open event”. Now I know better.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.