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()

End Sub

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:

Sub Auto_Open()

End Sub

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.

Application.Workbooks(“WorkbookX.xlsm”).RunAutoMacros xlAutoOpen

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.



Recommendations For You: