Excel Auto run Macro when 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