The sequence of events in workbooks

When you open a workbook Excel not only raises the Workbook_Open event but also a few other events. The same applies to switching worksheets, or closing the workbook. In this post I examine the sequence of activation/deactivation events in an Excel multiple window application, and with a ribbon custom tab. However, most of the findings also apply to normal single window applications without a custom tab, and also to Excel 2013.

In a multiple window application (or Multiple Document Interface, MDI) we can distinguish five levels:

Application - the top level;
Workbook    - within the active excel instance the user can switch to another workbook;
Window      - within the active workbook the user can switch to another window;
Sheet       - within the active window the user can switch to another sheet;
Range       - within the active sheet the user can switch to another cell.

Note that this is not a true hierarchical relationship, since the parent of a sheet is the workbook, not the window.

MDImodel

The conceptual model in the figure above presents an overview of the five levels and their Activate/Deactivate events. You may keep this in mind when reading on.
Note that on every level two events are raised when switching to another object. However, on Range level only an “Activate” event is fired (SelectionChange).

Opening the workbook

ExcelEvents1.xlsm is a workbook with two worksheets and two windows, and a custom tab with two cloned controls. In the workbook and worksheet modules all relevant events are defined. Most events have only two lines of code, to create a trace list in the VBE Immediate Window. Consider this trace list as an extension of the VBE Call Stack.
After opening the workbook the trace list is:

Workbook_Open
Workbook_Activate
Workbook_WindowActivate
Ribbon.customUIonLoad

At startup Excel not only fires a Workbook_Open but also a Workbook_Activate and a Workbook_WindowActivate event. Always in this sequence. Note that the Workbook_WindowActivate is always fired, even when your application has only one window. Since most Excel applications have a single window interface we usually don’t need this event.

Ribbon custom tab

The trace list above makes clear that the custom tab is loaded after the three workbook events. This gives you the opportunity to initialize any data structures that are used by the controls in the custom tab before these controls are loaded. For example, perhaps some controls must be hidden or disabled on startup. Or in Workbook_Open you determine the language to be used in the custom control labels.

CustomUIonload not always raised

CantExecuteInBreakModeIf you add a msgbox statement in the Workbook_Open handler, code execution will pause until the user closes the messagebox. Obvious. But now replace the msgbox by a Stop statement. Then something else happens: after re-opening the file you receive one or more messages saying that VBA can’t execute code in debug mode. After closing these messagebox(es) and continue running your code (F5) the Ribbon.customUIonLoad is missing in the trace list and indeed was not executed. However, the custom tab is installed.

More debugging trouble

Keep the Stop in the Workbook_Open handler and again re-open the app. Now use F8 to step through your code, and watch the trace list when you press F8 on “End Sub”. Conclusion: if you want to debug and step through your startup code then you have to set breakpoints in each fired events. Be aware of this potential debugging pitfall.

Disabled events or deferred events?

Now set EventEnabling to false in the Workbook_Open. Save and re-open, and see this trace list:

Workbook_Open
Ribbon.customUIonLoad

Again we see that the ribbon does things its own way, ignoring the events setting. (See Disable Shift key on open.) Indeed, the ribbon is not part of the Excel Object Model, just like activex controls and userforms. The two Activate events were not fired, as we could expect. However… are they really not fired? Do some switching between sheets and windows, and you will see that no new events occur. Obvious, we disabled them. But then go to the VBE Immediate Window and set eventenabling to true. Immediately the two “missing” events are fired! Or perhaps we should say: processed. They were fired at startup before events were disabled. Disabling events does not kill any events that were fired already. Processing was deferred until events were enabled again.

The startup window

At startup Excel always activates the last window. If your app has 5 windows then the window with WindowNumber = 5 will be activated, even when you saved it with window 3 active. This is important to note. If you want your user to start on the first window you might be tempted to activate window 1 at the end of Workbook_Open. It will do so, but remember: after the Workbook_Open a Window_Activate is fired that will activate window 5! Indeed, this can be very confusing. A simple workaround is to create your windows in reverse order. Or activate your preferred window later, in the Workbook_WindowActivate handler.

The missing Sheet activation

Note that in Open events series a Worksheet_Activate is missing. Unfortunately, I would argue. And perhaps a bit inconsistent. Excel sends us a message that the workbook is activated; Excel sends us a message that the window is activated. Why not send us a message that the sheet is activated too? Anyway, if you want your user to always start on Sheet1, and you need to do some initialization on Sheet1 you can use code like this on startup:

If Activesheet.Name = Sheet1.Name Then Sheet1.Initialize Else Sheet1.Activate

And in Worksheet_Activate of Sheet1 you call Me.Initialize. Note that only Sheet1.Activate is not sufficient: if Sheet1 is already the activesheet this event won’t fire.

Switching

Switching between Workbooks

Switching to another workbook (eg by creating a new workbook) gives this events list:

Workbook_WindowDeactivate
Workbook_Deactivate

And if the user switches back from another workbook (eg closes the new workbook):

Workbook_Activate
Workbook_WindowActivate

Again, note that there’s no SheetDeactivate or SheetActivate event fired. If you want for example a userform visible when SheetOne is active then you must trigger the show/hide of the userform from another event. The obvious event is the WindowDe/Activate, where you can Init/Exit the active sheet.

Switching between Windows
Workbook_WindowDeactivate 2
Workbook_WindowActivate 1

Still no SheetDe/activate events fired, even if the two windows show different sheets. See the previous paragraph for a solution if you need to do some inits/exits for a sheet.

Switching between Sheets
Worksheet_Deactivate SheetOne
Workbook_SheetDeactivate SheetOne
Worksheet_Activate SheetTwo
Workbook_SheetActivate SheetTwo

Finally, a SheetDe/activate! Note that first the lowest event level is fired. More on this later.

Closing the Workbook

When the user closes the workbook the trace list is:

Workbook_BeforeClose
Workbook_WindowDeactivate
Workbook_Deactivate

Keep in mind that the Before_Close event is not the last event being handled.
If the user has made changes and then closes the workbook, Excel will ask to save the changes. This is the trace list:

Workbook_BeforeClose
Workbook_BeforeSave
Workbook_AfterSave
Workbook_WindowDeactivate
Workbook_Deactivate

Application level events

In ExcelEvents2.xlsm I added class CApplicationEvents. This class traps all relevant application level events and adds them to the trace list. The object is created in Workbook_Open and destroyed in Workbook_BeforeClose. (That’s how to do it, right? Think about it a minute. Still sure? Let’s see…)
This is the trace list after opening the file:

Workbook_Open
Application_WorkbookOpen
Workbook_Activate
Application_WorkbookActivate
Workbook_WindowActivate
Application_WindowActivate
Ribbon.customUIonLoad

No surprises. Let’s move on.
After switching from SheetOne to SheetTwo in the active window we see:

Worksheet_Deactivate SheetOne
Workbook_SheetDeactivate SheetOne
Application_SheetDeactivate SheetOne
Worksheet_Activate SheetTwo
Workbook_SheetActivate SheetTwo
Application_SheetActivate SheetTwo

Handling the events starts on the lowest level, then moving up in the hierarchy.
Finally, this is the trace list after closing the file:

Workbook_BeforeClose
Workbook_WindowDeactivate
Workbook_Deactivate

But wait! Don’t we miss something here?! This is one of the pitfalls that can be hard to debug if you’re not aware of the sequence of events. Got the answer already? Spoiler follows. In the Workbook_BeforeClose handler we destroyed the clsApplicationEvents object. So it can’t listen anymore to the events that are fired after the BeforeClose! If you comment out the set-to-nothing line in the Workbook_BeforeClose handler then the trace list becomes:

Workbook_BeforeClose
Application_WorkbookBeforeClose
Workbook_WindowDeactivate
Application_WindowDeactivate
Workbook_Deactivate
Application_WorkbookDeactivate

Looks much better now! So it seemed to be the right place to kill the object in Before_Close but in fact this is too early. What to do? A simple workaround is: in the Workbook_BeforeClose set a switch Closing to true. In Workbook_Deactivate you add a line like:

If Closing Then Set clsApplicationEvents = Nothing

This solution is implemented in ExcelEvents3.xlsm, see next item.

Trapped workbook and worksheet events

Beside Application events we can also trap most of the Workbook and Worksheet events. In ExcelEvents3.xlsm we added two classes, one to trap Workbook events and one to trap Worksheet events. In the Workbook_Open handler now four objects are created to trap the events of the Application, of the Workbook and of the two Worksheets. Now, when we switch from SheetOne to SheetTwo in the active window, we get up to five sheet-deactivate and five sheet-activate events:

MyWorksheet_Deactivate SheetOne
Worksheet_Deactivate SheetOne
MyWorkbook_SheetDeactivate SheetOne
Workbook_SheetDeactivate SheetOne
MyApplication_SheetDeactivate SheetOne
MyWorksheet_Activate SheetTwo
Worksheet_Activate SheetTwo
MyWorkbook_SheetActivate SheetTwo
Workbook_SheetActivate SheetTwo
MyApplication_SheetActivate SheetTwo

Note that the trapped event is always processed first. However, there is one exception to this rule. After opening the workbook we see:

Workbook_Open
MyApplication_WorkbookOpen
MyWorkbook_Activate
Workbook_Activate
Etc

There is no MyWorkbook_Open event in the list. Since event trapping is set in the Workbook_Open handler it is not possible to trap this event before it is processed. Seems obvious, so actually there was no need to expose this event to the class.
You can use Events3.xlsm to do some more research on trapped events. If you want you can add other events to the classes, or add classes to trap embedded chart events, or querytable events. Whatever.

Talking about Charts

A chart can be either an embedded chart (contained in a ChartObject object) or a separate chart sheet (quote Microsoft MSDN). If the chart is embedded it behaves just like any other object that can live on a worksheet. For example, on a worksheet you can switch the selection between cells, charts, controls, images etc. You can create a class to trap embedded chart events, much like you create a class to trap querytable events. No big deal.
On the other hand, if the chart is a “chart sheet” it behaves just like a normal worksheet. Indeed, a chart sheet and a worksheet are both sheets, members of the sheets collection. In the module of a chart sheet you can define events, like you did in the worksheet module. Also you can define a class to trap these chart sheet events. Then, switching from SheetOne to a Chart sheet results in a trace list like this:

MyWorksheet_Deactivate SheetOne
Worksheet_Deactivate SheetOne
MyWorkbook_SheetDeactivate SheetOne
Workbook_SheetDeactivate SheetOne
MyApplication_SheetDeactivate SheetOne
MyChart_Activate Chart
Chart_Activate Chart
MyWorkbook_SheetActivate Chart
Workbook_SheetActivate Chart
MyApplication_SheetActivate Chart

No big deal either.

And what about Excel 2013?

MDI20102013Microsoft decided to move Excel 2013 from MDI to SDI. Does this change the event model as we described above? Quote from MSDN: “SDI means that each workbook will have its own top-level app window and will have its own corresponding ribbon. All existing application-level window methods, events, and properties are unaffected by this change. All existing workbook-level window methods, events, and properties now operate on the top-level window for that workbook.” So the answer is: no, not at all. The only – big – difference is the way the windows are presented to the user, see screenshots. In 2013 every window has it’s own ribbon and statusbar, which makes a multi window arrangement in 2013 much less attractive. Nevertheless, even in 2013 Excel will raise all events and event series as described above.

Conclusion

In this post I examined the sequence of events in a MDI Excel app with two windows. Most apps are SDI and are perhaps a bit less complicated. In any case, MDI or SDI, it is important to be aware of the events and event series that are fired by Excel and when and how they are processed. As we have seen this is not always obvious. An event driven multi window application requires good planning and good design. In upcoming posts I want to share some more on these issues. For now: I hope this pixcel helps. Excel!

Recommended reading

Chip Pearson: Events And Event Procedures In VBA

Comments

  1. Paolo says:

    Dear Frans,
    your post is very precoious, your work very sharp. MS should pay for your clear explaination!
    Thousands thanks!

  2. Beste Frans,

    Great article!
    Is Ribbon.customUIonLoad the last event when opening a workbook?
    I’m trying to prevent to possibility of changing the ribbon XML so that the visibility of Admin buttons can be changed.

  3. Stephen says:

    Dear Frans,

    Thank you SO much for this very helpful article! I was pulling my hair out trying to understand why certain events were being triggered (or not). This has solved all of those issues and I am very thankful.

  4. Tony says:

    When multiple files exist in the “At StartUp” location…..how does Excel determine which file first, second, etc?

Speak Your Mind

*