Prevent closing of workbook

Question

There is ABC.xls file which has the macro. Now, the macro has a sub which is getting called when I press Ctrl + M. This sub will open a Open File Dialog where the user can choose a CSV file. So basically, this macro is used for processing and saving CSV file. Below is the code which gets called on pressing Ctrl + M.

Sub runProperChangeSubroutine()             ' Assigned to shortcut key CTRL + M.
    file_name = ActiveWorkbook.Name         ' Gets the file name.
    Application.Run file_name & "!ChangeSub"
End Sub

When the user closes the workbook, I have to test for a condition like each row in the CSV has a termination string. If that termination string is not present, I should popup a message box to the user and prevent the closing of workbook. So I did the following...

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim improperRowNumber As Integer
    Dim BlnEventState As Boolean
    improperRowNumber = returnImproperRow()

    BlnEventState = Application.EnableEvents
    Application.EnableEvents = True

    If improperRowNumber <> -1 Then
        Cancel = True
        MsgBox "The row number " & improperRowNumber & " is not ending with '@/#'. Please correct the row before closing."
    Else
        Call saveCSV
    End If
    Application.EnableEvents = BlnEventState
End Sub

On clicking of the close (X mark on top right corner, closing of workboox. I am not closing the excel.) button, I am able to see the message box but the workboox closes. I want user to make some edit if the row is not properly ended. Please suggest.

Edited: As the above code is not working, I used ThisWorkbook.Saved = False after the message box as shown below...

If improperRowNumber <> -1 Then
    MsgBox "The row number " & improperRowNumber & " is not ending with '@/#'. Please correct the row before closing."
    Application.DisplayAlerts = False
    ThisWorkbook.Saved = False
    Cancel = False
Else

Now it displays the "Do you want to save the changes...." message box. If I click on Cancel button on message box, the workbook is not closed.

Is there a way to customize the message box text or buttons or hide this Save message box?

Answer 1

If you must monitor the closing of workbooks other than the one containing the macros, you can intercept application-level events from your macro-enabled workbook as follows:

Add (or adapt) this code behind ThisWorkbook:

Option Explicit

Private m_CloseHelper As CloseHelper

Private Sub Workbook_Open()
    Set m_CloseHelper = New CloseHelper
End Sub

Add a new Class Module named CloseHelper:

Option Explicit

Private WithEvents m_App As Excel.Application

Private Sub Class_Initialize()
    Set m_App = Excel.Application
End Sub

Private Sub Class_Terminate()
    Set m_App = Nothing
End Sub

Private Sub m_App_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
    'Logic goes here, e.g. the code below will prevent the user from closing
    'any workbook other than this one, for as long as this workbook is open.
    If Not Wb Is ThisWorkbook Then
        Cancel = True
        MsgBox "Hello from m_App_WorkbookBeforeClose"
    End If
End Sub

The important keyword is WithEvents, and the principle is that events raised by the Excel application can now be coded against within the CloseHelper class.

You'll find a more in-depth article here: Application Events