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 business analysis, project management, and also 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 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 *