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 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 *