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