VBA Excel Workbook.Open Event

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

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

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

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

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in creating custom Function and Sub solutions, and is proficient in report automation with Access.

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

Leave a Reply

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