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

Atiqul Haque

New Member
Joined
Dec 13, 2020
Messages
45
Office Version
  1. 2016
Platform
  1. Windows
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,
 

Attachments

  • Excel (X) Button.JPG
    Excel (X) Button.JPG
    32.4 KB · Views: 9

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Atiqul Haque

New Member
Joined
Dec 13, 2020
Messages
45
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Jan 22, 2021
Messages
1
Pushing this thread as I am experiencing this exact same weird problem!!!

To my understanding, the simple code

VBA 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!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,098
Messages
5,622,678
Members
415,921
Latest member
ExcelNoob28

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top