Why does the order of Excel VBA events differ when interacting with trusted vs untrusted macro enabled workbooks?

Question

I have performed an experiment to understand the order in which workbook events occur. I created two workbooks, i.e. Workbook1.xlsm and Workbook2.xlsm. Each workbook contains the following code, the only difference being that Workbook1.xlsm refers to Workbook1 in its log message and Workbook2.xlsm refers to Workbook2. The Log procedure writes a string to a log file. The code in both workbooks writes to the same log file.

Code in ThisWorkbook module:

Option Explicit

Private Sub Workbook_Activate()
        Log "Workbook 1: " & "Workbook_Activate Event"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
        Log "Workbook 1: " & "Workbook_BeforeClose Event"
End Sub

Private Sub Workbook_Deactivate()
        Log "Workbook 1: " & "Workbook_Deactivate Event"
End Sub

Private Sub Workbook_Open()
        Log "Workbook 1: " & "Workbook_Open Event"
End Sub

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
        Log "Workbook 1: " & "Workbook_WindowActivate Event"
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
        Log "Workbook 1: " & "Workbook_WindowDeactivate Event"
End Sub

Code in MLogging module:

Public Sub Log(ByVal sMsg As String)
    
    Dim sFullPath As String
    Dim iFileNum As Integer
    
    sFullPath = ThisWorkbook.Path & "\" & "Events" & ".log"
    
    iFileNum = FreeFile
    Open sFullPath For Append As #iFileNum
    Write #iFileNum, sMsg
    Close #iFileNum
    
End Sub

Procedure:

1. Open Excel and modify the Trust Center settings to add the folder containing the two workbook files as a trusted location.
2. Close Excel.
3. Open Workbook1.xlsm by double-clicking its icon in the Windows' File Explorer app.
4. Open Workbook2.xlsm by double-clicking its icon in the Windows' File Explorer app.
5. Change focus from Workbook1.xlsm to Workbook2.xlsm using <ALT>+<TAB>.
6. Change focus from Workbook2.xlsm to Workbook1.xlsm using <ALT>+<TAB>.
7. Close Workbook1 using the 'X' at the far right of Excel's title bar.
8. Close Workbook2 using the 'X' at the far right of Excel's title bar. This closes Excel.
9. Save the log file to Events - Trusted.log.
10. Open Excel and modify the Trust Center settings as follows:
    • Remove the folder containing the two workbook files from the Trusted Locations list.
    • Set the 'Disable Trusted Documents' option in the Trusted Documents tab.
11. Close Excel.
12. Open Workbook1.xlsm by double-clicking its icon in the Windows' File Explorer app.
13. Click the 'Enable Content' button next to the 'Macros have been disabled' security warning to enable macros in Workbook1.xlsm.
14. Open Workbook2.xlsm by double-clicking its icon in the Windows' File Explorer app.
15. Click the 'Enable Content' button next to the 'Macros have been disabled' security warning to enable macros in Workbook2.xlsm.
16. Change focus from Workbook1.xlsm to Workbook2.xlsm using <ALT>+<TAB>.
17. Change focus from Workbook2.xlsm to Workbook1.xlsm using <ALT>+<TAB>.
18. Close Workbook1.xlsm using the 'X' at the far right of Excel's title bar.
19. Close Workbook2.xlsm using the 'X' at the far right of Excel's title bar. This closes Excel.
20. Save the log file to Events - Untrusted.log.

Results:

Contents of log file 'Events - Trusted.log':

1. "Workbook 1: Workbook_Open Event"
2. "Workbook 1: Workbook_Activate Event"
3. "Workbook 1: Workbook_WindowActivate Event"
4. "Workbook 2: Workbook_Open Event"
5. "Workbook 1: Workbook_WindowDeactivate Event"
6. "Workbook 1: Workbook_Deactivate Event"
7. "Workbook 2: Workbook_Activate Event"
8. "Workbook 2: Workbook_WindowActivate Event"
9. "Workbook 2: Workbook_WindowDeactivate Event"
10. "Workbook 2: Workbook_Deactivate Event"
11. "Workbook 1: Workbook_Activate Event"
12. "Workbook 1: Workbook_WindowActivate Event"
13. "Workbook 1: Workbook_WindowDeactivate Event"
14. "Workbook 1: Workbook_Deactivate Event"
15. "Workbook 2: Workbook_Activate Event"
16. "Workbook 2: Workbook_WindowActivate Event"
17. "Workbook 2: Workbook_BeforeClose Event"
18. "Workbook 2: Workbook_WindowDeactivate Event"
19. "Workbook 2: Workbook_Deactivate Event"
20. "Workbook 1: Workbook_Activate Event"
21. "Workbook 1: Workbook_WindowActivate Event"
22. "Workbook 1: Workbook_BeforeClose Event"
23. "Workbook 1: Workbook_WindowDeactivate Event"
24. "Workbook 1: Workbook_Deactivate Event"

Contents of log file 'Events - Untrusted.log':

1. "Workbook 1: Workbook_Open Event"
2. "Workbook 1: Workbook_Activate Event"
3. "Workbook 1: Workbook_WindowDeactivate Event"
4. "Workbook 1: Workbook_Deactivate Event"
5. "Workbook 2: Workbook_Open Event"
6. "Workbook 2: Workbook_Activate Event"
7. "Workbook 2: Workbook_WindowDeactivate Event"
8. "Workbook 2: Workbook_Deactivate Event"
9. "Workbook 1: Workbook_Activate Event"
10. "Workbook 1: Workbook_WindowActivate Event"
11. "Workbook 1: Workbook_WindowDeactivate Event"
12. "Workbook 1: Workbook_Deactivate Event"
13. "Workbook 2: Workbook_Activate Event"
14. "Workbook 2: Workbook_WindowActivate Event"
15. "Workbook 2: Workbook_BeforeClose Event"
16. "Workbook 2: Workbook_WindowDeactivate Event"
17. "Workbook 2: Workbook_Deactivate Event"
18. "Workbook 1: Workbook_Activate Event"
19. "Workbook 1: Workbook_WindowActivate Event"
20. "Workbook 1: Workbook_BeforeClose Event"
21. "Workbook 1: Workbook_WindowDeactivate Event"
22. "Workbook 1: Workbook_Deactivate Event"

Findings of Interest:

1. When opening the workbooks, the Workbook_WindowActivate event does not occur when the workbook is untrusted. It only occurs when the workbook is trusted.
   • Executing procedure step 3 yields the following events in order:
     • Workbook1: Workbook_Open event.
     • Workbook1: Workbook_Activate event.
     • Workbook1: Workbook_WindowActivate event.
   • Executing procedure step 12 yields a single event:
     • Workbook1: Workbook_Open event.
     • Workbook1: Workbook_Activate event.
2. When the workbooks are trusted, and Workbook2 is opened, the Workbook1 Workbook_WindowDeactivate and Workbook_Deactivate events occur after the Workbook2 Workbook_Open event; yet, when the workbooks are untrusted, the Workbook1 Workbook_WindowDeactivate and Workbook_Deactivate events occur before the Workbook2 Workbook_Open event.
   • Executing procedure step 4 yields the following events in order:
     • Workbook2: Workbook_Open event.
     • Workbook1: Workbook_WindowDeactivate event.
     • Workbook1: Workbook_Deactivate event.
     • Workbook2: Workbook_Activate event.
     • Workbook2: Workbook_WindowActivate event.
   • Executing procedure steps 14 and 15 yields the following events in order:
     • Workbook1: Workbook_WindowDeactivate event.
     • Workbook1: Workbook_Deactivate event.
     • Workbook2: Workbook_Open event.
     • Workbook2: Workbook_Activate event.

Ramifications

1. In order to ensure macro-enabled workbooks can make changes to the Excel UI (e.g. hiding the Formula Bar) without affecting other macro enabled workbooks, it must be possible for the code of a workbook to execute and undo any modifications made to the Excel UI before the code of another workbook is able to modify them. Because the Workbook_Open event can occur before the Workbook_Deactivate event of an existing workbook, making UI changes in the Workbook_Open event would make it impossible for the existing workbook to restore UI settings without overwriting the changes made by the newly opened workbook. It appears that it would be best to use the Workbook_Activate event to save UI settings before modifying them, and to use the Workbook_Deactivate event to restore the UI settings. But this convention would have to be honored by all macro enabled workbooks.

Questions

1. First and foremost, why is there a difference in the events and their order depending on whether the workbooks are trusted or untrusted?
2. Why, when opening an untrusted workbook, is there no Workbook_WindowActivate event, but there is when the workbook is trusted?
3. Why, when the workbooks are trusted, and Workbook2 is opened, does the Workbook1 Workbook_WindowDeactivate and Workbook_Deactivate events occur after the Workbook2 Workbook_Open event; yet, when the workbooks are untrusted, the Workbook1 Workbook_WindowDeactivate and Workbook_Deactivate events occur before the Workbook2 Workbook_Open event?
4. Is there any way to ensure a consistent event order irrespective of trusted status; and that the Workbook_Open event of another workbook being opened will always occur after the Workbook_Deactivate event of the current workbook?
5. Does a standard convention exist for saving and restoring UI changes?

ADDENDUM

This is an addendum to my original post. I performed another experiment which captures workbook related Application events when a trusted and untrusted workbook is opened and then closed.

Setup

I created two workbooks: Workbook1.xlsm to capture the application events and Workbook2.xlsm to capture the workbook events. The idea is to open Workbook1.xlsm first so that it can enable the application events, and then open and close Workbook2.xlsm that will capture the workbook events.

In Workbook1, there is code to capture the following application events.

1. Application_WindowActivate
2. Application_WindowDeactivate
3. Application_WorkbookActivate
4. Application_WorkbookBeforeClose
5. Application_WorkbookDeactivate
6. Application_WorkbookOpen In Workbook2, there is code to capture the following workbook events.
7. Workbook_Activate
8. Workbook_BeforeClose
9. Workbook_Deactivate
10. Workbook_Open
11. Workbook_WindowActivate
12. Workbook_WindowDeactivate

Procedure:

1. Place Workbook1.xlsm in folder Temp1. Place Workbook2.xlsm in folder Temp2.
2. Open Excel.
3. Modify the Trust Center to uncheck the 'Disable Trusted Documents' checkbox.
4. Modify the Trust Center to add Temp1 and Temp2 as Trusted Locations.
5. Close Excel.
6. Open Workbook1.xlsm.
7. Open Workbook2.xlsm.
8. Close Workbook2.xlsm.
9. Close Workbook1.xlsm.
10. Save log file to Events - Trusted.log
11. Open Excel.
12. Modify the Trust Center to remove Temp2 from the Trusted Locations list.
13. Close Excel.
14. Open Workbook1.xlsm.
15. Open Workbook2.xlsm.
16. Click 'Enable Content' to enable Workbook2.xlsm
17. Close Workbook2.xlsm.
18. Close Workbook1.xlsm.
19. Save log file to Events - Untrusted.log

Log Contents:

The log contents are displayed side by side with identical events aligned for ease of identifying differences. The application events and workbook events associated with Workbook2.xlsm (Wkb2) are bolded as they are of most interest. The unnumbered entries are labels added to provide context. They do not occur in the actual log files.

Events-Trusted.log	Events-Untrusted.log
(Open Workbook1.xlsm)	(Open Workbook1.xlsm)
01. Wbk1: Application_WorkbookOpen	01. Wkb1: Application_WorkbookOpen
02. Wkb1: Application_WorkbookActivate	02. Wkb1: Application_WorkbookActivate
03. Wkb1: Application_WindowActivate	03. Wkb1: Application_WindowActivate
(Open Workbook2.xlsm)	(Open Workbook2.xlsm)
04. Wkb2: Workbook_Open
05. Wkb2: Application_WorkbookOpen	04. Wkb2: Application_WorkbookOpen *
06. Wkb1: Application_WindowDeactivate	05. Wkb1: Application_WindowDeactivate
07. Wkb1: Application_WorkbookDeactivate	06. Wkb1: Application_WorkbookDeactivate
08. Wkb2: Workbook_Activate
09. Wkb2: Application_WorkbookActivate	07. Wkb2: Application_WorkbookActivate *
10. Wkb2: Workbook_WindowActivate
11. Wkb2: Application_WindowActivate	08. Wkb2: Application_WindowActivate
	(Click Enable Content button)
	09. Wkb2: Workbook_Open
	10. Wkb2: Application_WorkbookOpen *
	11. Wkb2: Workbook_Activate
	12. Wkb2: Application_WorkbookActivate *
(Close Workbook2.xlsm)	(Close Workbook2.xlsm)
12. Wkb2: Workbook_BeforeClose	13. Wkb2: Workbook_BeforeClose
13. Wkb2: Application_WorkbookBeforeClose	14. Wkb2: Application_WorkbookBeforeClose
14. Wkb2: Workbook_WindowDeactivate	15. Wkb2: Workbook_WindowDeactivate
15. Wkb2: Application_WindowDeactivate	16. Wkb2: Application_WindowDeactivate
16. Wkb2: Workbook_Deactivate	17. Wkb2: Workbook_Deactivate
17. Wkb2: Application_WorkbookDeactivate	18. Wkb2: Application_WorkbookDeactivate
18. Wkb1: Application_WorkbookActivate	19. Wkb1: Application_WorkbookActivate
19. Wkb1: Application_WindowActivate	20. Wkb1: Application_WindowActivate
(Close Workbook1.xlsm)	(Close Workbook1.xlsm)
20. Wkb1: Application_WorkbookBeforeClose	21. Wkb1: Application_WorkbookBeforeClose
21. Wkb1: Application_WindowDeactivate	22. Wkb1: Application_WindowDeactivate
22. Wkb1: Application_WorkbookDeactivate	23. Wkb1: Application_WorkbookDeactivate

Findings of Interest:

1. In the untrusted case, when Workbook2.xlsm is opened, the application events Application_WorkbookOpen and Application_WorkbookActivate occur twice. Once when Excel first opens the Workbook2.xlsm workbook and a second time after the Enable Contents button is clicked. In the log contents, the relevant events are postpended with an asterisk. The Application_WindowActivate event does not follow suit. It seems to me that there should be a Workbook_WindowActivate event and an Application_WindowActivate event following the second Application_WorkbookActivate event (entry 34).

Questions:

1. Looking at the untrusted case, can anyone provide a reason why a Workbook_WindowActivate event and an Application_WindowActivate event do not occur following the Application_WorkbookActivate event (entry 34)?

Answer 1

I don't have time to properly answer all of your questions but I use the following code to ensure consistency:

Option Explicit

'Variable used to postpone code execution in case the workbook is protected when
'   opened. If protected, then postpone the code from Workbook_Open event until
'   the Workbook_Activate event is triggered
Private m_isOpenDelayedUntilActive As Boolean

'Variable event to keep track if the Workbook_Open event actually fired
'It can happen, especially when another book is already opened, that the
'   Workbook_Open event is not triggered
Private m_didOpenEventFire As Boolean

'Events
Private Sub Workbook_Activate()
    If m_isOpenDelayedUntilActive Then
        m_isOpenDelayedUntilActive = False
        InitWorkbook
    End If
End Sub
Private Sub Workbook_Open()
    m_didOpenEventFire = True
    Dim objProtectedViewWindow As ProtectedViewWindow
    '
    On Error Resume Next
    Set objProtectedViewWindow = Application.ProtectedViewWindows(Me.Name)
    On Error GoTo 0
    '
    m_isOpenDelayedUntilActive = Not objProtectedViewWindow Is Nothing
    If Not m_isOpenDelayedUntilActive Then InitWorkbook
End Sub

'*******************************************************************************
'Trigger Workbook_Open if needed. Could be called from CustomUI Init
'https://stackoverflow.com/a/63328366/8488913
'*******************************************************************************
Friend Sub FireOpenEventIfNeeded(Optional dummyVarToMakeProcHidden As Boolean)
    If Not m_didOpenEventFire Then Workbook_Open
End Sub

'*******************************************************************************
'Validate Application version and call any relevant methods
'*******************************************************************************
Private Sub InitWorkbook()
    If Val(Application.Version) < 12 Then
        MsgBox "This Workbook requires Excel 2007 or later!" & vbNewLine _
             & "Book will close!", vbCritical, "Closing"
        Me.Close False
        Exit Sub
    End If
    '
    'Place call to outside procedure(s) here
    '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
    
    '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
End Sub

Regardless of the events order, what eventually gets executed is the InitWorkbook method. I also call FireOpenEventIfNeeded from a Custom UI initialization because of a bug in older versions of Excel.

Answer 2

This answer is a summary of my findings rather than an answer to the questions I posed. It is important to note that the following findings are inferred from a very limited number of experiments (the experiments performed are documented in the question post). Consequently, I cannot say with certainty that the results apply to all cases.

Terminology clarifications:

Trusted Workbook

This is a workbook that is considered trusted by the Excel Trust Center. The workbook macros will be allowed to execute without any intervention by the user.

Untrusted Workbook

The workbook is not considered trusted by the Excel Trust Center. When an untrusted workbook is opened, the message bar will appear displaying a security warning along with an 'Enable Content' button to allow the user to enable macros..

Findings:

All references to 'workbook' imply a macro enabled workbook.

1. Workbook Events:

   • Opening a trusted workbook:

     • The workbook event order is as follows:
       Workbook_Open
Workbook_Activate
Workbook_WindowActivate

   • Opening an untrusted workbook:

     • The workbook event order is as follows:
       Workbook_Open
Workbook_Activate
     • The Workbook_WindowActivate event will not fire.
     • The Workbook_Open event will not fire until the user has clicked the 'Enable Content' button.

   • Closing a workbook when macros are enabled:

     • The workbook event order is as follows:
       Workbook_BeforeClose
Workbook_WindowDeactivate
Workbook_Deactivate

   • Opening a second workbook when there is an existing workbook open with macros enabled:

     • If the workbook being opened is trusted:

       • The workbook event order is as follows:
         Workbook_Open...............(opening workbook)
Workbook_WindowDeactivate...(existing workbook)
Workbook_Deactivate.........(existing workbook)
Workbook_Activate...........(opening workbook)
Workbook_WindowActivate.....(opening workbook)

     • If the workbook being opened is untrusted:

       • The workbook event order is as follows:
         Note: there is no Workbook_WindowActivate event. See 'Opening an untrusted workbook' above.
         Workbook_WindowDeactivate...(existing workbook)
Workbook_Deactivate.........(existing workbook)
Workbook_Open...............(opening workbook)
Workbook_Activate...........(opening workbook)
       • The Workbook_WindowDeactivate and Workbook_Deactivate events of the existing workbook will occur prior to the user clicking the 'Enable Content' button for the workbook being opened.
       • The Workbook_Open event of the workbook being opened will not fire until the user clicks the 'Enable Content' button.
2. Application Events:
   • Opening a trusted workbook:
     • The application event order is as follows:
       Application_WorkbookOpen
Application_WorkbookActivate
Application_WindowActivate
   • Opening an untrusted workbook:
     • The application event order is as follows:
       Note: the Application_WorkbookOpen and Application_WorkbookActivate events occur twice.
       Application_WorkbookOpen
Application_WorkbookActivate
Application_WindowActivate
Application_WorkbookOpen (after user clicks 'Enable Content' button)
Application_WorkbookActivate