2013\/01\/31\u00a0by\u00a0frans<\/a><\/div>\n
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.<\/p>\n
In a multiple window application (or Multiple Document Interface, MDI) we can distinguish five levels:<\/p>\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n<\/td>\n
Application - the top level;<\/code><\/div>\nWorkbook\u00a0\u00a0\u00a0 - within the active excel instance the user can switch to another workbook;<\/code><\/div>\nWindow\u00a0\u00a0\u00a0\u00a0\u00a0 - within the active workbook the user can switch to another window;<\/code><\/div>\nSheet\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 - within the active window the user can switch to another sheet;<\/code><\/div>\nRange\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 - within the active sheet the user can switch to another cell.<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\nNote that this is not a true hierarchical relationship, since the parent of a sheet is the workbook, not the window.<\/p>\n
<\/a><\/p>\n
<\/div>\nThe 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.
\nNote that on every level two events are raised when switching to another object. However, on Range level only an \u201cActivate\u201d event is fired (SelectionChange).<\/p>\nOpening the workbook<\/h3>\nExcelEvents1.xlsm<\/a>\u00a0is 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.
\nAfter opening the workbook the trace list is:<\/p>\n
\n\n\n\n\n\n1<\/div>\n2<\/div>\n3<\/div>\n4<\/div>\n<\/td>\n \n\nWorkbook_Open<\/code><\/div>\nWorkbook_Activate<\/code><\/div>\nWorkbook_WindowActivate<\/code><\/div>\nRibbon.customUIonLoad<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\nAt 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\u00a0always<\/em>\u00a0fired, even when your application has only one window. Since most Excel applications have a single window interface we usually don\u2019t need this event.<\/p>\n
Ribbon custom tab<\/h5>\nThe trace list above makes clear that the custom tab is loaded\u00a0after<\/em>\u00a0the 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.<\/p>\n
CustomUIonload not always raised<\/h5>\n<\/a><\/p>\n
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\u2019t 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.<\/p>\n
More debugging trouble<\/h5>\nKeep 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 \u201cEnd Sub\u201d. 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.<\/p>\n
Disabled events or deferred events?<\/h5>\nNow set EventEnabling to false in the Workbook_Open. Save and re-open, and see this trace list:<\/p>\n
\n\n\n\n\n\n1<\/div>\n2<\/div>\n<\/td>\n \n\nWorkbook_Open<\/code><\/div>\nRibbon.customUIonLoad<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\nAgain we see that the ribbon does things its own way, ignoring the events setting. (See\u00a0Disable Shift key on open<\/a>.) 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\u2026 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 \u201cmissing\u201d 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.<\/p>\n
The startup window<\/h5>\nAt 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.<\/p>\n
The missing Sheet activation<\/h5>\nNote 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:<\/p>\n
\n\n\n\n\n\n1<\/div>\n<\/td>\n \n\nIf Activesheet.Name = Sheet1.Name Then Sheet1.Initialize Else Sheet1.Activate<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\nAnd 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\u2019t fire.<\/p>\n
Switching<\/h3>\n
Switching between Workbooks<\/h5>\nSwitching to another workbook (eg by creating a new workbook) gives this events list:<\/p>\n
\n\n\n\n\n\n1<\/div>\n2<\/div>\n<\/td>\n \n\nWorkbook_WindowDeactivate<\/code><\/div>\nWorkbook_Deactivate<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\nAnd if the user switches back from another workbook (eg closes the new workbook):<\/p>\n
\n\n\n\n\n\n1<\/div>\n2<\/div>\n<\/td>\n \n\nWorkbook_Activate<\/code><\/div>\nWorkbook_WindowActivate<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\nAgain, note that there\u2019s 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.<\/p>\n
Switching between Windows<\/h5>\n\n\n\n\n\n\n1<\/div>\n2<\/div>\n<\/td>\n \n\nWorkbook_WindowDeactivate 2<\/code><\/div>\nWorkbook_WindowActivate 1<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\nStill 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.<\/p>\n
Switching between Sheets<\/h5>\n\n\n\n\n\n\n1<\/div>\n2<\/div>\n3<\/div>\n4<\/div>\n<\/td>\n \n\nWorksheet_Deactivate SheetOne<\/code><\/div>\nWorkbook_SheetDeactivate SheetOne<\/code><\/div>\nWorksheet_Activate SheetTwo<\/code><\/div>\nWorkbook_SheetActivate SheetTwo<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\nFinally, a SheetDe\/activate! Note that first the lowest event level is fired. More on this later.<\/p>\n
Closing the Workbook<\/h3>\nWhen the user closes the workbook the trace list is:<\/p>\n
\n\n\n\n\n\n1<\/div>\n2<\/div>\n3<\/div>\n<\/td>\n \n\nWorkbook_BeforeClose<\/code><\/div>\nWorkbook_WindowDeactivate<\/code><\/div>\nWorkbook_Deactivate<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\nKeep in mind that the Before_Close event is not the last event being handled.
\nIf the user has made changes and then closes the workbook, Excel will ask to save the changes. This is the trace list:<\/p>\n
\n\n\n\n\n\n1<\/div>\n2<\/div>\n3<\/div>\n4<\/div>\n5<\/div>\n<\/td>\n \n\nWorkbook_BeforeClose<\/code><\/div>\nWorkbook_BeforeSave<\/code><\/div>\nWorkbook_AfterSave<\/code><\/div>\nWorkbook_WindowDeactivate<\/code><\/div>\nWorkbook_Deactivate<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\nApplication level events<\/h3>\nIn\u00a0ExcelEvents2.xlsm<\/a>\u00a0I 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\u2019s how to do it, right? Think about it a minute. Still sure? Let\u2019s see\u2026)
\nThis is the trace list after opening the file:<\/p>\n
\n\n\n\n\n\n1<\/div>\n2<\/div>\n3<\/div>\n4<\/div>\n5<\/div>\n6<\/div>\n7<\/div>\n<\/td>\n \n\nWorkbook_Open<\/code><\/div>\nApplication_WorkbookOpen<\/code><\/div>\nWorkbook_Activate<\/code><\/div>\nApplication_WorkbookActivate<\/code><\/div>\nWorkbook_WindowActivate<\/code><\/div>\nApplication_WindowActivate<\/code><\/div>\nRibbon.customUIonLoad<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\nNo surprises. Let\u2019s move on.
\nAfter switching from SheetOne to SheetTwo in the active window we see:<\/p>\n
\n\n\n\n\n\n1<\/div>\n2<\/div>\n3<\/div>\n4<\/div>\n5<\/div>\n6<\/div>\n<\/td>\n \n\nWorksheet_Deactivate SheetOne<\/code><\/div>\nWorkbook_SheetDeactivate SheetOne<\/code><\/div>\nApplication_SheetDeactivate SheetOne<\/code><\/div>\nWorksheet_Activate SheetTwo<\/code><\/div>\nWorkbook_SheetActivate SheetTwo<\/code><\/div>\nApplication_SheetActivate SheetTwo<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\nHandling the events starts on the lowest level, then moving up in the hierarchy.
\nFinally, this is the trace list after closing the file:<\/p>\n
\n\n\n\n\n\n1<\/div>\n2<\/div>\n3<\/div>\n<\/td>\n \n\nWorkbook_BeforeClose<\/code><\/div>\nWorkbook_WindowDeactivate<\/code><\/div>\nWorkbook_Deactivate<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\nBut wait! Don\u2019t we miss something here?! This is one of the pitfalls that can be hard to debug if you\u2019re 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\u2019t listen anymore to the events that are fired\u00a0after<\/em>\u00a0the BeforeClose! If you comment out the set-to-nothing line in the Workbook_BeforeClose handler then the trace list becomes:<\/p>\n
\n\n\n\n\n\n1<\/div>\n2<\/div>\n3<\/div>\n4<\/div>\n5<\/div>\n6<\/div>\n<\/td>\n \n\nWorkbook_BeforeClose<\/code><\/div>\nApplication_WorkbookBeforeClose<\/code><\/div>\nWorkbook_WindowDeactivate<\/code><\/div>\nApplication_WindowDeactivate<\/code><\/div>\nWorkbook_Deactivate<\/code><\/div>\nApplication_WorkbookDeactivate<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\nLooks much better now! So it\u00a0seemed<\/em>\u00a0to 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:<\/p>\n
\n\n\n\n\n\n1<\/div>\n<\/td>\n \n\nIf Closing Then Set clsApplicationEvents = Nothing<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\nThis solution is implemented in ExcelEvents3.xlsm, see next item.<\/p>\n
Trapped workbook and worksheet events<\/h3>\nBeside Application events we can also trap most of the Workbook and Worksheet events. In\u00a0ExcelEvents3.xlsm<\/a>\u00a0we 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:<\/p>\n
\n\n\n\n\n\n1<\/div>\n2<\/div>\n3<\/div>\n4<\/div>\n5<\/div>\n6<\/div>\n7<\/div>\n8<\/div>\n9<\/div>\n10<\/div>\n<\/td>\n \n\nMyWorksheet_Deactivate SheetOne<\/code><\/div>\nWorksheet_Deactivate SheetOne<\/code><\/div>\nMyWorkbook_SheetDeactivate SheetOne<\/code><\/div>\nWorkbook_SheetDeactivate SheetOne<\/code><\/div>\nMyApplication_SheetDeactivate SheetOne<\/code><\/div>\nMyWorksheet_Activate SheetTwo<\/code><\/div>\nWorksheet_Activate SheetTwo<\/code><\/div>\nMyWorkbook_SheetActivate SheetTwo<\/code><\/div>\nWorkbook_SheetActivate SheetTwo<\/code><\/div>\nMyApplication_SheetActivate SheetTwo<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\nNote that the trapped event is always processed first. However, there is one exception to this rule. After opening the workbook we see:<\/p>\n
\n\n\n\n\n\n1<\/div>\n2<\/div>\n3<\/div>\n4<\/div>\n5<\/div>\n<\/td>\n \n\nWorkbook_Open<\/code><\/div>\nMyApplication_WorkbookOpen<\/code><\/div>\nMyWorkbook_Activate<\/code><\/div>\nWorkbook_Activate<\/code><\/div>\nEtc<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\nThere 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.
\nYou 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.<\/p>\n
Talking about Charts<\/h3>\nA 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.
\nOn the other hand, if the chart is a \u201cchart sheet\u201d 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:<\/p>\n
\n\n\n\n\n\n1<\/div>\n2<\/div>\n3<\/div>\n4<\/div>\n5<\/div>\n6<\/div>\n7<\/div>\n8<\/div>\n9<\/div>\n10<\/div>\n<\/td>\n \n\nMyWorksheet_Deactivate SheetOne<\/code><\/div>\nWorksheet_Deactivate SheetOne<\/code><\/div>\nMyWorkbook_SheetDeactivate SheetOne<\/code><\/div>\nWorkbook_SheetDeactivate SheetOne<\/code><\/div>\nMyApplication_SheetDeactivate SheetOne<\/code><\/div>\nMyChart_Activate Chart<\/code><\/div>\nChart_Activate Chart<\/code><\/div>\nMyWorkbook_SheetActivate Chart<\/code><\/div>\nWorkbook_SheetActivate Chart<\/code><\/div>\nMyApplication_SheetActivate Chart<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\nNo big deal either.<\/p>\n
And what about Excel 2013?<\/h3>\n<\/a><\/p>\n
Microsoft decided to move Excel 2013 from MDI to SDI. Does this change the event model as we described above? Quote from MSDN: \u201cSDI 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.\u201d So the answer is: no, not at all. The only \u2013 big \u2013 difference is the way the windows are presented to the user, see screenshots. In 2013 every window has it\u2019s 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.<\/p>\n
Conclusion<\/h3>\nIn 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!<\/p>\n
 <\/p>\nSource:\u00a0\u00a0\u00a0Events in Excel Workbooks<\/a><\/p>\n
Related :\u00a0Events And Event Procedures In VBA<\/a><\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
The sequence of events in workbooks 2013\/01\/31\u00a0by\u00a0frans 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…<\/p>\n
 Lees Meer  Lees Meer<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_crdt_document":"","_uag_custom_page_level_css":"","footnotes":""},"categories":[8],"tags":[],"post_folder":[128],"class_list":["post-146","post","type-post","status-publish","format-standard","hentry","category-excel"],"uagb_featured_image_src":{"full":false,"thumbnail":false,"medium":false,"medium_large":false,"large":false,"1536x1536":false,"2048x2048":false,"post-thumbnail":false,"nisarg-full-width":false,"wpsso-schema-1x1":false,"wpsso-schema-4x3":false,"wpsso-schema-16x9":false,"wpsso-thumbnail":false,"wpsso-opengraph":false,"wpsso-tc-summary":false,"wpsso-tc-lrgimg":false},"uagb_author_info":{"display_name":"maliepro","author_link":"https:\/\/www.ronmaliepaard.nl\/blog\/author\/maliepro\/"},"uagb_comment_info":0,"uagb_excerpt":"The sequence of events in workbooks 2013\/01\/31\u00a0by\u00a0frans 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…","_links":{"self":[{"href":"https:\/\/www.ronmaliepaard.nl\/blog\/wp-json\/wp\/v2\/posts\/146","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ronmaliepaard.nl\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ronmaliepaard.nl\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ronmaliepaard.nl\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ronmaliepaard.nl\/blog\/wp-json\/wp\/v2\/comments?post=146"}],"version-history":[{"count":0,"href":"https:\/\/www.ronmaliepaard.nl\/blog\/wp-json\/wp\/v2\/posts\/146\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.ronmaliepaard.nl\/blog\/wp-json\/wp\/v2\/media?parent=146"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ronmaliepaard.nl\/blog\/wp-json\/wp\/v2\/categories?post=146"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ronmaliepaard.nl\/blog\/wp-json\/wp\/v2\/tags?post=146"},{"taxonomy":"post_folder","embeddable":true,"href":"https:\/\/www.ronmaliepaard.nl\/blog\/wp-json\/wp\/v2\/post_folder?post=146"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}