Disable excel's "Close" button (red X) in workbook for more than 1 click

Hi all

I have seen a few threads on this topic and have applied the basic below code in to the object "ThisWorkbook". It unfortunately only works for the first click and once dismissing the message box has occurred the close (x) button can then be pushed and operates as normal.

I would like to completely disable closing the workbook in this manner and rather direct the user to a button to close the workbook.

Here is my code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Cancel = True
MsgBox "Please use the 'Save and Close' button on Sheet 1"
End Sub

The interesting thing is that it will work for infinite clicks only if I have 2 workbooks open with both of them having the above code in the "ThisWorkbook" object?!
Please note I want this to prevent a close of the workbook and NOT A USER FORM.

Thanks in advance

Try this code

'In Workbook Module
'------------------
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Not CloseMode Then
        Cancel = True
        MsgBox "Please Use The Button To Close This File"
    End If
End Sub

'In Standard Module
'------------------
Public CloseMode As Boolean

Sub CloseMe()
    CloseMode = True
    ThisWorkbook.Save
    Application.Quit
End Sub

What do you mean with the first click? It is supposed that it will disable x close button of the application then you can use a button to close and save .. If you used a button the application will be closed
I tried to click more than once and it doesn't respond and I could only close using the button on the worksheet

This doesn't happen for me .. Try this attachment

I am using 2016 version and no specific settings for that .. Waiting for other experts

Looks like this issue has been encountered a number of times on other forums without solution (other than obviously just using the excel version/computer that works!).

maybe you should revisit why you want to use the before_close and maybe there is another way of looking at the problem, assuming this is a significant issue on your side.