VBA Word/Outlook - Customer User Form Cancels Event Handler

Question

I have some strange behaviour from an event handling function for closing a word document. I'm using Word's DocumentBeforeClose event handler in my Outlook module. In the handler, I prompt the user with a message that asks do they want to finalise the document, discard the document, or keep editing.

If I use the MsgBox function with vbYesNoCancel buttons - then the event handler fires every time I close the Word document. This works as expected.

If I use a custom user form with three buttons ("Finalise", "Discard", "Continue Editing"), then the event handler only fires the first time the Word document is closed. If the user clicks Continue Editing, then the next time they close the document, the events handler isn't fired.

I don't understand why these two cases cause different behaviour? Why is the events handler cancelled if I use my custom user form?

Events Handler Class (Not working version)

Option Explicit

Private WithEvents mWordApp As word.Application

Private Sub mWordApp_DocumentBeforeClose(ByVal Doc As document, Cancel As Boolean)
    Dim msgBoxResponse As String
    
    'This code brings Outlook back to the active window so the user can response to the form
    AppActivate Application.ActiveExplorer.Caption
    SendKeys "%"
    
    Set finaliseUserForm = New UserFormFinaliseRFI
    finaliseUserForm.show
    msgBoxResponse = finaliseUserForm.response
    Unload finaliseUserForm
    Set finaliseUserForm = Nothing
    
    'msgBoxResponse = MsgBox("Do you want to finalise the document?", vbYesNoCancel)
    
    If msgBoxResponse = "Finalise" Then
    'If msgBoxResponse = vbYes Then
        Set mWordApp = Nothing
    Else
        Cancel = True
        AppActivate "test.docx"
    End If
End Sub

Public Sub StartEvents()
    Set mWordApp = CreateObject("Word.Application")
End Sub

Public Sub OpenWordDocument(filePath As String)
    mWordApp.Documents.Open filePath
    mWordApp.Visible = True
End Sub

Events Handler Class (Working version)

Option Explicit

Private WithEvents mWordApp As word.Application

Private Sub mWordApp_DocumentBeforeClose(ByVal Doc As document, Cancel As Boolean)
    Dim msgBoxResponse As String
    
    'This code brings Outlook back to the active window so the user can response to the form
    AppActivate Application.ActiveExplorer.Caption
    SendKeys "%"
    
    'Set finaliseUserForm = New UserFormFinaliseRFI
    'finaliseUserForm.show
    'msgBoxResponse = finaliseUserForm.response
    'Unload finaliseUserForm
    'Set finaliseUserForm = Nothing
    
    msgBoxResponse = MsgBox("Do you want to finalise the document?", vbYesNoCancel)
    
    'If msgBoxResponse = "Finalise" Then
    If msgBoxResponse = vbYes Then
        Set mWordApp = Nothing
    Else
        Cancel = True
        AppActivate "test.docx"
    End If
End Sub

Public Sub StartEvents()
    Set mWordApp = CreateObject("Word.Application")
End Sub

Public Sub OpenWordDocument(filePath As String)
    mWordApp.Documents.Open filePath
    mWordApp.Visible = True
End Sub

Test Module Sub

Option Explicit

Private mEvents As WordEventsHelper

Public Sub testEvents()
    Set mEvents = New WordEventsHelper
    mEvents.StartEvents
    mEvents.OpenWordDocument "\(mypath)\test.docx"
    AppActivate "test.docx"
End Sub

User Form Code

Private mResponse As String

Public Property Get response() As String
    response = mResponse
End Property

Private Sub CommandButtonFinalise_Click()
    mResponse = "Finalise"
    Me.Hide
End Sub

Private Sub CommandButtonDiscard_Click()
    mResponse = "Discard"
    Me.Hide
End Sub

Private Sub CommandButtonContinueEditing_Click()
    mResponse = "Continue Editing"
    Me.Hide
End Sub

Did you try to debug the code? Do you get correct results with a user form? — Eugene Astafiev, Sep 28 '22 at 21:19
Yes, everything seems to work correctly - user form included. Except that is for the fact that the event handler only fires the first time when using the custom user form. — user1357607, Sep 28 '22 at 21:20
Make sure you set the `Cancel` parameter to true to get the event fired again. — Eugene Astafiev, Sep 28 '22 at 21:23
I set Cancel to true in the 'else' section of the events handler function. That executes correctly regardless of whether it's my customer user form or the msgbox. It's just that when I use the user form, the handler never fires again. — user1357607, Sep 28 '22 at 21:42
I have tried using 'Unload Me' in the user form click event handlers instead of hide - it doesn't change the behaviour...it only means I have to extract the option the user has chosen using a different method. — user1357607, Sep 28 '22 at 22:55
As a test, I put the 'Cancel = True' line prior to loading the user form - and then commented out the code that set it lower down. The event handler still fails to fire after the first time. — user1357607, Sep 29 '22 at 00:11

score 0 · Answer 1 · answered Sep 28 '22 at 21:21

0

There are several aspects when using user forms instead of message boxes. First, the user form is not aware about the parent window handle. Second, the user form is not modal. Third, use the Unload instead of Hide. See Changing the parent workbook window of an add-in's userform for more information.

answered Sep 28 '22 at 21:21

Eugene Astafiev

47,483
3
24
45

I don't think my user form uses (or needs to use) the parent window handle. There is no handles in my user form code. The code that changes the active window and cancels the closing event is in the event handler. I'm not sure what the relevance of unload and hide is? I use 'hide' in my user form so that it doesn't disappear before I am able to get the value of mResponse. The error remains even if I add the line Unload finaliseUserForm before setting the finaliseUserForm to Nothing – user1357607 Sep 28 '22 at 21:48
Even if I use the 'Unload me' line inside the user form button click code - the undesired behaviour remains. – user1357607 Sep 29 '22 at 00:27
Is your user form modal? How does it stop the event handler from being stopped before the form is closed? – Eugene Astafiev Sep 29 '22 at 06:07
The user form pauses execution of the event handler when it's open. The execution continues when I click a button and it's hidden and/or unloaded. – user1357607 Sep 29 '22 at 06:15
How does the code look like which works correctly with a message box? – Eugene Astafiev Sep 29 '22 at 06:34
In my example code in the original question, the working code is the commented out msgbox line and the commented out if statement. – user1357607 Sep 29 '22 at 22:03
I've added a working version of the code to my original question. – user1357607 Sep 29 '22 at 22:27

Cristian Buse · Answer 2 · 2022-10-07T08:55:21.847

I would have added a comment but it would be too long.

Replace this part:

Set finaliseUserForm = New UserFormFinaliseRFI
finaliseUserForm.show
msgBoxResponse = finaliseUserForm.response
Unload finaliseUserForm
Set finaliseUserForm = Nothing

with:

With New UserFormFinaliseRFI
    .Show vbModal
    msgBoxResponse = .response
End With

There is no need to call Unload or Set finaliseUserForm = Nothing as the new form instance that you created will get terminated by VB itself at some point and moreover the Unload method has been causing bugs and crashes in various Office versions and should be avoided.

Also, you might want to prevent the user clicking the X button on the form. Add this to your form code:

'Handles click on the X (close) button
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = vbFormControlMenu Then
        Cancel = True
    End If
End Sub

to ignore the X button or something like:

'Handles click on the X (close) button
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = vbFormControlMenu Then
        Cancel = True
        mResponse = "Discard"
        Me.Hide
    End If
End Sub

to get a specific behaviour.

Thanks. The first part of your answer is a good tip -it's a lot cleaner than what I was doing. However, it doesn't resolve the issue (at least not for me). Also there is a bug in your code as you reference finaliseUserForm.response instead of just .response. I actually have a separate function which removes the 'X' button from all my custom user forms. It's got nothing to do with the bug, so it's part of the unnecessary code I've removed. — user1357607, Oct 07 '22 at 00:10
@user1357607 Yes, apologies, I wrote the ```With``` block directly in the answer without going through VBA and testing. Removed the ```finaliseUserForm``` part. If the issue is still there then please check 2 things: 1) that the ```Application.EnableEvents``` is still on; 2) that the ```mWordApp``` still has an instance / state after the first ```DocumentBeforeClose``` event has ran and was cancelled. — Cristian Buse, Oct 07 '22 at 08:58

VBA Word/Outlook - Customer User Form Cancels Event Handler

2 Answers2