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