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 trick to learn is to make your macros or 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 comes in use.  I find a common use for this trick is when applying message boxes or “Pop-Ups” to reports, for this you need the macro to run automatically when the file opens, and having the ability to put in these messages proves very useful.

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:

 

 

 

 

 

Did you notice how this was achieved?

The trick is all 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 file is opened.  So if you want to amend another macro so that it runs automatically all you need to do is change the name of the macro to Auto_Open(), make sure you save the file and that’s 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 so you can apply this technique.

Keep Excelling,

 

Share

Leave a Reply

You must be logged in to post a comment.