Excel Workbook.Activate Event

Excel Workbook_Activate 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.

To understand when to use Excel Workbook_Activate Event, you must understand the meaning of “activate”.  Have you ever used ALT+PrintScreen? Normal PrintsScreen button in keyboard can capture the whole screen in your monitor, but ALT+PrintScreen only capture the “active” Windows. Take a look at the below print screen.

assignMacro1

To capture the above screen, first I click on the Record Macro box (Windows), it floats on top of other applications, then press ALT+PrintScreen. Similarly, “activate” a workbook means to click on it.

The Excel Workbook_Activate Event is triggered when

1) Switch from one workbook to another workbook containing Workbook_Activate Event (will not trigger Event if you switch from other application such as MS Word to workbook)

2) Open a workbook

There is another Event called Workbook_Open, which is triggered when a workbook is open. Both Workbook_Open and Workbook_Activate can be triggered when a workbook is opened, but if both of them are present in a workbook, Workbook_Open will trigger first before Workbook_Activate.

Example of  Excel Workbook_Activate Event

1) Press ALT+11 to enter VBE (Visual Basic Editor)

2) Double click on ThisWorkbook > select Workbook > Select Activate

Workbook_Activate_01

Insert your code between Private Sub and End Sub. For example, the below code assigns hotkey Crtl+Shift+k to Macro1 when Workbook is Activated.

Private Sub Workbook_Activate()
    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/ff823078%28v=office.15%29.aspx

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 *