Creating Excel VBA Macros That Run On Opening A Workbook
In Excel VBA, as well as macros that can be run manually, it is possible to create macros which are executed automatically when a certain event takes place in the Excel environment. One such event is the opening of a workbook. There are two principal methods of creating macros which run when a particular workbook is opened, both of which involve placing code inside the workbook itself.
One technique is to create an event-handling macro within the code module of the workbook. The second is to create a macro in a regular module and give it the special name “Auto_Open”.
To create an event-handling macro, double-click the “ThisWorkbook” object in the Project Explorer window of the Visual Basic Editor. This opens the code window of the workbook object. Next, choose “Workbook” from the drop-down menu in the top left of the code window. Excel will automatically create the default event-handling subroutine for a workbook object which just happens to be the “Open” event. Your code window should now contain the following subroutine:
Private Sub Workbook_Open()
Now all you have to do is to insert the code you would like to run when the workbook is opened.
For method two, you must begin by inserting a regular VBA module, by choosing Module from the Insert menu. Inside the module, enter the following code:
Here, once more, just insert any code you would like to execute when the workbook is opened.
There is not much difference between the two techniques; they achieve a similar result. Nevertheless, there is one key difference between them. The “Auto_Open” macro will only execute if you manually open the file: if the file is opened programmatically by another macro, the “Auto_Open” macro will be ignored. By contrast, the event-handling macro will run whenever the workbook is opened, either manually or programmatically.
If you are writing code which opens a workbook with an “Auto_Open” macro inside it, you can still launch the macro by writing a line of code similar to the following.
If you would like to learn more about Excel training courses, visit Macresource Computer Training, a UK IT training company offering Excel training courses at their central London training centre.