PDA

View Full Version : disable close "X" button



oilfield
01-24-2017, 03:24 AM
Hello,

I need your help. I would like to disable the close "X" button requiring the user to click on a control button that I have placed on the spreadsheet. Here is the code I have tried:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Cancel = True
MsgBox "Please use the button to close this file"
End Sub

The problem is this. This code works fine when the user clicks the close button the first time. If the user clicks the button a second time, the standard Save, Don't Save and Cancel window appears. How can I prevent this from happening? What needs to be changed or added to the above code?
Thank you in advance for your help!

GTO
01-24-2017, 04:14 AM
I tried this (code posted at #1) both with the WB in saved and unsaved status, using both the close wb button and close application button and the code repeatedly blocked (cancelled) exiting the wb.

oilfield
01-24-2017, 07:00 AM
It only works for me on the first try. Any suggestions?

GTO
01-24-2017, 07:03 AM
The only thing I can think of is put a Stop above Cancel = True and step through the code. Other than that, by chance is there any code that you are not showing us that disables events?

Mark

YasserKhalil
01-24-2017, 01:20 PM
See this link
http://www.excelforum.com/showthread.php?t=944850