Preventing Excel prompt from Word VBA

Question

I'm working with VBA on Word and Excel. I have the Word running a userform which will automatically open an Excel file. User should fill some data in Excel file and then go back to the Word userform. When user finish filling all fields in Word userform, it will run some VBA code on Word that copy data from Excel to Word. After finished, the Excel file will be closed automatically. Therefore, I need to prevent user from closing the Excel app manually.

In order to do that, I use these code in Excel VBA in Sub Workbook_BeforeClose. If user close the Excel application window, it will show a message box that ask whether the user is still working with the Word userform. The code as follows:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

answer = MsgBox("Are you still working with Word userform?", vbYesNo)
If answer = vbYes Then
    Cancel = True
    MsgBox "This workbook should not be closed. It will be automatically closed when you finish working with Ms. Word Template Userform."
Else
    Application.ThisWorkbook.Saved = True
End If
End Sub

In the Word VBA, I have code to close the Excel file:

Sub closeExcelApp()

If Not excelApp Is Nothing Then
    excelApp.DisplayAlerts = False
    excelWb.Close savechanges:=False
    Set excelWb = Nothing
    excelApp.Quit
End If
Set excelApp = Nothing

End Sub

This Sub will be called when the Word VBA code done copying data from Excel to Word. However, calling this Sub will cause the Workbook_BeforeClose called. Meanwhile, I don't want the Workbook_BeforeClose called when I call this closeExcelApp sub from Word VBA.

Any suggestion?

Answer 1

You can just disable events:

Sub closeExcelApp()

If Not excelApp Is Nothing Then
    excelApp.DisplayAlerts = False
    excelApp.EnableEvents = False
    excelWb.Close savechanges:=False
    Set excelWb = Nothing
    excelApp.Quit
End If
Set excelApp = Nothing

End Sub

Answer 2

As explained in comments, add this line on top of the modules : Public ClosingFromWord As Boolean and set this boolean to False when you start your code execution.

As you are working between apps, the easiest way will be to write the value of the boolean in a cell in Excel from Word and read/laod this value in the Workbook_BeforeClose to avoid going through the whole code of that routine.

And modify you code to look like this :

Sub closeExcelApp()
ClosingFromWord = True
excelApp.Workbooks(1).Sheets(1).Cells(1,1) = ClosingFromWord

If Not excelApp Is Nothing Then
    excelApp.DisplayAlerts = False
    excelWb.Close savechanges:=False
    Set excelWb = Nothing
    excelApp.Quit
End If
Set excelApp = Nothing

ClosingFromWord = False
excelApp.Workbooks(1).Sheets(1).Cells(1,1) = ClosingFromWord
End Sub

So while you execute closeExcelApp, the boolean will be set to True and the Workbook_BeforeClose won't be executed in his totality as it'll be exited with If ClosingFromWord Then Exit Sub :

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ClosingFromWord = Workbooks(1).Sheets(1).Cells(1,1)
If ClosingFromWord Then Exit Sub

answer = MsgBox("Are you still working with Word userform?", vbYesNo)
If answer = vbYes Then
    Cancel = True
    MsgBox "This workbook should not be closed. It will be automatically closed when you finish working with Ms. Word Template Userform."
Else
    Application.ThisWorkbook.Saved = True
End If
End Sub