How to make Excel macros run automatically on file open

Macros, whether recorded or written using VBA, are an increasing part of the Excel analysts toolkit. A useful technique to learn is how to make your macros or VBA scripts run automatically when the Excel file opens. Read on to find out how…

Situations

There are lots of times where having a macro run automatically when an Excel file is opened is beneficial.  A common use for this technique is when you need to apply message boxes or “Pop-Ups” to reports which communicate some information to the User and for this you need the macro to run automatically when the file opens.

There are also times when Excel is a very useful “go-between” tool for data.  You might have raw data in SQL Server and a report that needs to be in PowerPoint, in that case you could use Excel to read in the raw data and put it into tables, charts and so on before transferring to PowerPoint.

How to

Open up a new Excel workbook and save the file as an Excel Macro-Enabled file, you can call the file anything you like.

The next stage is to open up the Visual Basic Editor (Shortcut command ALT and F11) and insert the following script into a new module:

Sub Auto_Open()

MsgBox “Hello and welcome to this month’s Excel Report”

End Sub

 

 

 

 

 

 

 

 

 

 

Now you can save your file and exit.  When you re-open the file you will now get a message box that appears like so:

 

 

 

 

 

How this was achieved?

The secret is contained with the name of the VBA script, or your macro name.  In this case our VBA script for the message box reads:

Sub Auto_Open()

MsgBox “Hello and welcome to this month’s Excel Report”

End Sub

And it is the Auto_Open() name that makes the code run automatically when the Excel file is opened.  If you want to amend another Excel macro so that it will run automatically all you need to do is change the name of the macro to Auto_Open() then make sure you save the file and that is all there is to it.

You can now apply this knowledge to your existing macros where you need them to run when the file opens, or perhaps start looking at ways to include messages or pop-ups in your reporting that will benefit other users.

Keep Excelling,

 

Share/Bookmark

Leave a Comment