VBA & Macro Problems

Prevent Closing an Excel Workbook With the X Button Using VBA

Camila MilosovichCamila Milosovich Nov 20, 2026 869 views

Question details

Allow an Excel workbook to close through an ActiveX button while blocking ordinary close requests and retaining existing cleanup code.

Prevent Closing an Excel Workbook With the X Button Using VBA
Product
Microsoft Excel
Device & OS
not provided
Scenario
The workbook already has a Workbook_BeforeClose event and an ActiveX button intended to control closing.
Observed behavior
The user needs to combine close prevention and cleanup in the single Workbook_BeforeClose event.
Before you start

Open the macro-enabled workbook in Excel, enable its macros if you trust the file, and locate the existing Workbook_BeforeClose procedure in the VBA editor. Make sure the Developer tab is available so you can edit the ActiveX button.

Solution 1Recommended

Combine Close Prevention and Cleanup in Workbook_BeforeClose

Use a public flag to allow closing from the ActiveX button while canceling other close requests.

A workbook can have only one Workbook_BeforeClose procedure. Add the flag check to the existing procedure rather than creating a second one.

This controls normal close requests while macros are running; it is not a security measure and cannot prevent forced termination or closing when macros are disabled.

1
Add the close macro

Press Alt+F11 to open the VBA editor. Choose Insert > Module and add: Public Ok2Close As Boolean Public Sub CloseUsingButton() On Error GoTo CloseError Ok2Close = True ThisWorkbook.Close Ok2Close = False Exit Sub CloseError: Ok2Close = False MsgBox "The workbook could not be closed: " & Err.Description, vbExclamation End Sub

2
Update the existing close event

In the VBA editor, open ThisWorkbook and modify the existing Workbook_BeforeClose procedure so its beginning follows this pattern. Keep your existing cleanup code after the flag check: Private Sub Workbook_BeforeClose(Cancel As Boolean) Cancel = Not Ok2Close If Cancel Then Exit Sub Ok2Close = False ' Place the existing cleanup code here. End Sub

3
Connect the ActiveX button

In Excel, select Developer > Design Mode, then double-click the ActiveX button. In its existing Click procedure, call CloseUsingButton. For example: Private Sub CommandButton1_Click() CloseUsingButton End Sub Use the button's actual procedure name if it differs.

4
Test both close paths

Turn off Developer > Design Mode. Try the workbook's X button to confirm the close is canceled, then use the ActiveX button and confirm that cleanup runs and the workbook closes. Respond to any save prompt as needed.

Combine Close Prevention and Cleanup in Workbook_BeforeClose
Review existing quit logic: The example closes the workbook, not the entire Excel application. Review any existing Application.Quit call separately: it can affect other open workbooks and may trigger additional close events.
Free Microsoft Office alternative

Work With Excel Workbooks in WPS Office

WPS Office is a free, lightweight Office alternative with a familiar spreadsheet interface and support for common Excel formats, making it easy to move everyday workbook work between applications. It does not fix Excel's VBA close event, and VBA or ActiveX behavior may differ.

Open and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interface for everyday workbook tasksWork with spreadsheets in a lightweight Office suiteMove common spreadsheet content between WPS Office and Microsoft Office
microsoft office alternative - wps office

Frequently Asked Questions

Can I add a second Workbook_BeforeClose event?

No. Open ThisWorkbook in the VBA editor and combine the close-prevention check with your existing Workbook_BeforeClose procedure.

Where should I declare Ok2Close?

In the VBA editor, select Insert > Module and declare Public Ok2Close As Boolean at the top of the standard module.

How do I connect the macro to an ActiveX button?

Select Developer > Design Mode, double-click the button, and add a call to CloseUsingButton inside its Click procedure. Turn off Design Mode before testing.

Will this prevent users from closing the workbook if macros are disabled?

No. Workbook_BeforeClose cannot run when macros are disabled, and the code cannot prevent forced termination. It should not be treated as a security control.

Will closing the workbook also quit Excel?

ThisWorkbook.Close closes the workbook, but it does not explicitly quit Excel. An Application.Quit call can affect other open workbooks, so review it separately.