Re: Prevent or disable workbook close(X) button, and closed by a command button.

[Atiqul Haque]

Dear All Good Evening,

With the following codes to disable workbooks close(X) button, first time it works but when i click it again the workbook get closed.

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


I need the following:
#1. Disable or prevent to close entire workbook by normal excel close(X) button. &
#2. Workbook should closed by a vba command button.

Best Rgds,

 

[Atiqul Haque]

Dear All Good Evening,

With the following codes to disable workbooks close(X) button, first time it works but when i click it again the workbook get closed.

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


I need the following:
#1. Disable or prevent to close entire workbook by normal excel close(X) button. &
#2. Workbook should closed by a vba command button.

Best Rgds,

#3 Also i want to close the workbook by logout button.

 

[itchy00]

Pushing this thread as I am experiencing this exact same weird problem!!!

To my understanding, the simple code

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

should prevent a closing of the "file" (deliberately using this not very precise term here).

I see it all works fine with the workbook itself. When I use File->Close, for example, or the close(X) button in the Windows task bar when multiple Workbooks are opened, the behaviour is as expected.... I can click as many times as I want and the workbook simply stays opened.
But using the Application Close(X) Button in the top right corner, stopping/cancelling the closing is working only once. Clicking a second time still closes the whole Excel application.

I'm aware of differences between the running Excel Application/Instance and the open workbook, and that the BeforeClose() is only a sub of the workbook but not the app.
And I also found some vague statements in other forums that this event might not always be fired.

Nevertheless... how do I prevent the open Excel file from being closed whatsoever, regardless if the User Action is File->Close, workbook close, or clicking on the App's top right corner X button???????

Otherwise I really have a problem because the user can simply "override" my custom Saving routine which is checking for some specific cell contents. Second click on the X just opens the Save dialog and and regardless if "Save" or "Don't Save" was clicked (it does not save changes in both cases) the workbook plus the app are closed.


I'm wondering if this is a very specific problem (maybe even some kind of "bug") since there is close to zero information about this behaviour on the web.... So any help is appreciated!!!