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:
- Open Excel and modify the Trust Center settings to add the folder
containing the two workbook files as a trusted location.
- Close Excel.
- Open Workbook1.xlsm by double-clicking its icon in the Windows' File Explorer app.
- Open Workbook2.xlsm by double-clicking its icon in the Windows' File Explorer app.
- Change focus from Workbook1.xlsm to Workbook2.xlsm using <ALT>+<TAB>.
- Change focus from Workbook2.xlsm to Workbook1.xlsm using <ALT>+<TAB>.
- Close Workbook1 using the 'X' at the far right of Excel's title bar.
- Close Workbook2 using the 'X' at the far right of Excel's title bar. This closes Excel.
- Save the log file to Events - Trusted.log.
- 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.
- Close Excel.
- Open Workbook1.xlsm by double-clicking its icon in the Windows' File Explorer app.
- Click the 'Enable Content' button next to the 'Macros have been disabled' security warning to enable macros in Workbook1.xlsm.
- Open Workbook2.xlsm by double-clicking its icon in the Windows' File Explorer app.
- Click the 'Enable Content' button next to the 'Macros have been disabled' security warning to enable macros in Workbook2.xlsm.
- Change focus from Workbook1.xlsm to Workbook2.xlsm using <ALT>+<TAB>.
- Change focus from Workbook2.xlsm to Workbook1.xlsm using <ALT>+<TAB>.
- Close Workbook1.xlsm using the 'X' at the far right of Excel's title bar.
- Close Workbook2.xlsm using the 'X' at the far right of Excel's title bar. This closes Excel.
- Save the log file to Events - Untrusted.log.
Results:
Contents of log file 'Events - Trusted.log':
- "Workbook 1: Workbook_Open Event"
- "Workbook 1: Workbook_Activate Event"
- "Workbook 1: Workbook_WindowActivate Event"
- "Workbook 2: Workbook_Open Event"
- "Workbook 1: Workbook_WindowDeactivate Event"
- "Workbook 1: Workbook_Deactivate Event"
- "Workbook 2: Workbook_Activate Event"
- "Workbook 2: Workbook_WindowActivate Event"
- "Workbook 2: Workbook_WindowDeactivate Event"
- "Workbook 2: Workbook_Deactivate Event"
- "Workbook 1: Workbook_Activate Event"
- "Workbook 1: Workbook_WindowActivate Event"
- "Workbook 1: Workbook_WindowDeactivate Event"
- "Workbook 1: Workbook_Deactivate Event"
- "Workbook 2: Workbook_Activate Event"
- "Workbook 2: Workbook_WindowActivate Event"
- "Workbook 2: Workbook_BeforeClose Event"
- "Workbook 2: Workbook_WindowDeactivate Event"
- "Workbook 2: Workbook_Deactivate Event"
- "Workbook 1: Workbook_Activate Event"
- "Workbook 1: Workbook_WindowActivate Event"
- "Workbook 1: Workbook_BeforeClose Event"
- "Workbook 1: Workbook_WindowDeactivate Event"
- "Workbook 1: Workbook_Deactivate Event"
Contents of log file 'Events - Untrusted.log':
- "Workbook 1: Workbook_Open Event"
- "Workbook 1: Workbook_Activate Event"
- "Workbook 1: Workbook_WindowDeactivate Event"
- "Workbook 1: Workbook_Deactivate Event"
- "Workbook 2: Workbook_Open Event"
- "Workbook 2: Workbook_Activate Event"
- "Workbook 2: Workbook_WindowDeactivate Event"
- "Workbook 2: Workbook_Deactivate Event"
- "Workbook 1: Workbook_Activate Event"
- "Workbook 1: Workbook_WindowActivate Event"
- "Workbook 1: Workbook_WindowDeactivate Event"
- "Workbook 1: Workbook_Deactivate Event"
- "Workbook 2: Workbook_Activate Event"
- "Workbook 2: Workbook_WindowActivate Event"
- "Workbook 2: Workbook_BeforeClose Event"
- "Workbook 2: Workbook_WindowDeactivate Event"
- "Workbook 2: Workbook_Deactivate Event"
- "Workbook 1: Workbook_Activate Event"
- "Workbook 1: Workbook_WindowActivate Event"
- "Workbook 1: Workbook_BeforeClose Event"
- "Workbook 1: Workbook_WindowDeactivate Event"
- "Workbook 1: Workbook_Deactivate Event"
Findings of Interest:
- 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.
- 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
- 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
- First and foremost, why is there a difference in the events and their order depending on whether the workbooks are trusted or untrusted?
- Why, when opening an untrusted workbook, is there no Workbook_WindowActivate event, but there is when the workbook is trusted?
- 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?
- 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?
- 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.
- Application_WindowActivate
- Application_WindowDeactivate
- Application_WorkbookActivate
- Application_WorkbookBeforeClose
- Application_WorkbookDeactivate
- Application_WorkbookOpen
In Workbook2, there is code to capture the following workbook events.
- Workbook_Activate
- Workbook_BeforeClose
- Workbook_Deactivate
- Workbook_Open
- Workbook_WindowActivate
- Workbook_WindowDeactivate
Procedure:
- Place Workbook1.xlsm in folder Temp1. Place Workbook2.xlsm in folder Temp2.
- Open Excel.
- Modify the Trust Center to uncheck the 'Disable Trusted Documents' checkbox.
- Modify the Trust Center to add Temp1 and Temp2 as Trusted Locations.
- Close Excel.
- Open Workbook1.xlsm.
- Open Workbook2.xlsm.
- Close Workbook2.xlsm.
- Close Workbook1.xlsm.
- Save log file to Events - Trusted.log
- Open Excel.
- Modify the Trust Center to remove Temp2 from the Trusted Locations list.
- Close Excel.
- Open Workbook1.xlsm.
- Open Workbook2.xlsm.
- Click 'Enable Content' to enable Workbook2.xlsm
- Close Workbook2.xlsm.
- Close Workbook1.xlsm.
- 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:
- 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:
- 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)?
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.
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.
- 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:
- 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