cba Disable Close Button for Excel 2016

Question

I have written simple vba code for disabling close button:

Step-1 Declared CloseBook as Boolean in one of the modules

Step-2 

Private Sub Workbook_BeforeClose(Cancel As Boolean)

   CloseBook = False

   If Not CloseBook Then

       Cancel = True

       MsgBox "Please Use Button on Main Menu to Close the Workbook"

   End If

   CloseBook = False

End Sub

It works once but if the Workbook close button is used for the second time, it shows the normal message, Save, Don't Save or Cancel.

Need help.

Answer 1

If EnableEvents were false when the X button was clicked then Workbook_BeforeClose would not be called, so setting it true in Workbook_BeforeClose is not useful.

Happy to try the file.  Bill at Manville dot org dot uk . Or you could put it on a file-sharing website.

I confess I haven't tried in 2016 as I don't have it on the machine I am using today, but repeated X in 2013 call Workbook_BeforeClose every time for me.

Answer 2

I suspect there is some difference between the code you posted and your actual code.

It should always work, and even prevent your button code from closing the workbook since the variable CloseBook will always be False when you test it.

If you had set Application.EnableEvents =  False then you would get the Save changes dialog on subsequent attempts to close.

Bill

I appreciate your help but it does not work. I have slightly modified the code and declared Event Enabler as True as first line in the code. I have done it on a fresh file to test but fails. I am reproducing the complete code below.

In Thisworkbook

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Application.EnableEvents = True

    Cancel = Not ok2Close

End Sub

In Module

Public ok2Close As Boolean

Sub CloseMacro()

    Application.EnableEvents = True

    ok2Close = True

    ThisWorkbook.Close SaveChanges:=True

         Application.Quit

     ok2Close = False

 End Sub

It fails second time when I try through X application button. I am ready to send the file if you can share E-mail address.

Thanks in advance.

JC_56

Answer 3

I suspect there is some difference between the code you posted and your actual code.

It should always work, and even prevent your button code from closing the workbook since the variable CloseBook will always be False when you test it.

If you had set Application.EnableEvents =  False then you would get the Save changes dialog on subsequent attempts to close.