Excel Auto run Macro when workbook is opened

Excel Auto run Macro when workbook is opened

Excel Auto Run Macro is used when you need to call a Sub Procedure or run your actions when an Workbook is opened. For example, you can set special hotkey for Workbook when a Workbook is opened.

One common use of Excel Auto run Macro is that people make application in Excel User Form and they want the Form look like a non-Excel, real Windows application. When user opens the Workbook, a Form pops up and the Excel application (the Excel background) is hidden.

You may ask why people do this instead of doing a real Visual Basic Application. The reason is that using VB.Net application very likely requires installation of .NET Framework but that would require Administration privilege, which could be a problem if you are not an IT administrator at workplace.

To me, I use Auto run Macro to apply formatting after I export data from Access to Excel. I cannot pre-format Excel because somehow Access would screw up the pre-formatting.

There are two ways to Auto run Macro when Excel is open.

Method 1: Excel Auto run Macro using Auto_Open

The below method uses Auto_Open, which is an old approach for auto run Macro before Workbook_Open Event was implemented.

1) ALT+11 to enter VBE (Visual Basic Editor)

2) Insert Module

3) Insert the below code

Sub Auto_Open()
End Sub

4) If you already have a Sub Procedure called Macro1, then type

Sub Auto_Open()
    Macro1
End Sub

If you want to auto run User Form called UserForm1, then type

Sub Auto_Open()
    UserForm1.Show
End Sub

Method 2: Excel Auto run Macro using Workbook_Open Event

The below method uses Workbook_Open Event, a newer approach but has the same function as Auto_Open.

1) Press ALT+11 to enter VBE (Visual Basic Editor)

2) Double click on ThisWorkbook > select Workbook > Select Open

hotkey_03

3) Insert your code between Private Sub and End Sub. For example, the below code assigns hotkey Crtl+Shift+k to Macro1 when Workbook is Opened

Private Sub Workbook_Open()
    Application.OnKey "^+k", "Macro1"
End Sub

If you want to know more about OnKey Method, click here

Outbound References

https://msdn.microsoft.com/en-us/library/office/ff194819%28v=office.15%29.aspx

https://www.youtube.com/watch?v=-VFirshgu9w

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *