Disable application close button X

I want to disable close button of workbook and allow closure through macro. I have tried several methods including the simplest of all codes:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
   Cancel = True
End Sub

It works once but second time I click on the close button, it asks standard question (Save, Don't Save, Cancel).

I have another question: When I close through Macro, will Workbook_BeforeClose(Cancel As Boolean) not interfere?

Simplified version of Macro for closing workbook is :

Sub myClose()

    .....

    .......

    .......

    ThisWorkbook.Save
    Application.Quit
End Sub

Need Help.

 

Question Info


Last updated July 23, 2019 Views 1,621 Applies to:

Place beforeclose event code in Thisworkbook module

Private Sub Workbook_BeforeClose(Cancel As Boolean)
     Cancel = Not Ok2Close
End Sub

Place this macro in General module

Public Ok2Close As Boolean

Sub CloseMacro()
   Ok2Close = True
   ThisWorkbook.Close SaveChanges:=True
        Application.Quit
    Ok2Close = False
End Sub

Gord

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Gord,

Thanks for your response but it does not work. I have included one more line in both the codes to set Application.EventEnable as True (first line). It always fails second time if click on X button of application. I can share the file. but how?

Regards,

JC_56

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

I do not have Office 2016 but the code I posted works fine in Excel 2007.

There are many file-sharing sites out there. . . . .OneDrive is good

Here are instructions for that. . . . .

https://support.microsoft.com/en-us/help/17184/windows-10-onedrive

Or send to me. . . . . .gorddibbATshawDOTca

Gord

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Hello Gord.

I was unable to work on my project for several days due to some exigency. Now I am back with my work. I am thankful for guiding me to use OneDrive. I have uploaded my file at following link. I am not sure where is the mistake. I am not a vba expert.

https://1drv.ms/x/s!AhKWPbKPCde6a6Ai7PHyVf6_jxg

Truly appreciate your help.

Best regards.

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Gord

The key issue with my file is that I wish to disable application close button (X). The Application Close button works once. Second time it closes the file. I want to close file through worksheet Macro. You will find that I have written two vba codes, one for This workbook and other for the "Save & Close" button (in blue color) on the worksheet. I am unable to ascertain why X button code does not work when I click on it for the second time.

Additionally, thanks for helping me in removing "Remove personal... " warning.

Regards,

JC_56

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

I have no problem closing your workbook without getting the message.

I cannot test with Excel 2016 version so will have to bow out and defer to someone who can assist you with that particular version of Excel.

Good luck.

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.