VBA Excel Workbook_Open Event
Excel predefines some popular actions that you would do on different Objects (worksheet, workbook, button, etc), those actions are called Event. For example, activating a worksheet is an Event, closing a workbook is an Event, clicking on a button is an event. Each Object has its own list of Events, Workbook has a list of Events (e.g. close workbook, open workbook), worksheet has a list of Events (e.g. activate worksheet, edit a Cell).
If you perform an Event, say, closing a workbook, your desired code can be triggered. For example, you may want to save a workbook automatically when you close a workbook, or you may want a welcome message box to pop up when a workbook is opened. Event is a Sub Procedure (begin with Private Sub and end with End Sub) and is generated automatically (see in the below section) with a specific name, you can call a Sub Procedure or write your own code within the Event code.
One common use of Workbook_Open Event 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 Workbook_Open Event 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.
Example of Excel Workbook_Open Event
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
Note that Workbook_Open Event can be skipped by holding SHIFT button, but you need to open that workbook from another workbook. Simply put, open workbook A, hold shift, open workbook B
Alternative for Excel Workbook_Open Event 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
Outbound References
https://msdn.microsoft.com/en-us/library/office/ff194819%28v=office.15%29.aspx
