2013/01/31 by frans

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:

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

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:

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

If 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:

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:

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:

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

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

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

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:

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:

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:

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

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

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:

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:

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:

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

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:

No big deal either.

And what about Excel 2013?

Microsoft 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!

 

Source:   Events in Excel Workbooks

Related : Events And Event Procedures In VBA