VBA & Macro Problems

How to Allow a UserForm to Close an Excel Workbook While Disabling the X Button

Steven ParkerSteven Parker Nov 10, 2026 870 views

Question details

The user wants to allow closing an Excel workbook exclusively via a VBA UserForm, disabling the standard application close (X) button.

How to Allow a UserForm to Close a Workbook While Preventing the X Button
Product
Excel
Device & OS
not provided
Scenario
Developing a VBA application where workbook closure needs to be controlled strictly through a custom UserForm interface rather than the default window controls.
Observed behavior
By default, the Excel window's X button closes the workbook, bypassing the custom UserForm logic. The goal is to programmatically restrict closure to the UserForm.
Before you start

Ensure you have enabled the Developer tab and have access to the VBA Editor (ALT + F11). Save your workbook as a Macro-Enabled Workbook (.xlsm) to prevent losing your code.

Solution 1Recommended

Use a Public Boolean Flag to Control Workbook Closure

Define a public variable to act as a flag. The Workbook_BeforeClose event will check this flag to determine whether to allow the workbook closure or cancel it.

By setting a workbook-level variable, you can distinguish between an approved exit triggered by your UserForm and an unauthorized exit attempted via the Excel application's X button.

1
Declare a Public Variable

Open the VBA Editor (ALT + F11) and insert a new Standard Module (Insert > Module). Declare a public boolean variable at the top of the module, for example: 'Public AllowClose As Boolean'.

2
Add Code to the UserForm Close Button

In your UserForm code module, add the exit logic to your custom close button. Set the flag to True before initiating the close: 'AllowClose = True', followed by 'ThisWorkbook.Close SaveChanges:=True'.

3
Intercept the Workbook_BeforeClose Event

Double-click 'ThisWorkbook' in the Project Explorer on the left. Choose 'Workbook' from the left dropdown to create the 'Workbook_BeforeClose' event. Inside it, check the flag: 'If Not AllowClose Then Cancel = True'. You can also add a MsgBox here to tell users to use the form to exit.

4
Reset the Flag (Optional)

If the close process is somehow interrupted or the user cancels saving, ensure you reset 'AllowClose = False' so the restriction remains in place for subsequent actions.

Use a Public Boolean Flag to Control Workbook Closure
Macro Security Requirement: Make sure your users have macros enabled. If macros are disabled, the BeforeClose event will not trigger, and the X button will close the workbook normally.
Use WPS Spreadsheet for VBA

Run and Edit VBA Macros Seamlessly in WPS Spreadsheet

WPS Spreadsheet offers robust built-in VBA support, allowing you to create UserForms, write macros, and manage workbook events just like in Excel. It is fully compatible with .xlsm files and Excel's VBA syntax.

  1. 1. Open your Macro Workbook: Launch WPS Office and open your .xlsm file directly in WPS Spreadsheet.
  2. 2. Access the Developer Tab: Navigate to the 'Developer' tab on the top ribbon to access all macro tools.
  3. 3. Launch the VBA Editor: Click on 'Visual Basic' or press ALT + F11 to open the VBA Editor, where you can modify your UserForms and global variables.
Fully compatible with Microsoft Excel macro-enabled formats (.xlsm, .xlsb).Advanced VBA Editor to design UserForms and manage Workbook_BeforeClose events.Free, lightweight, and fast alternative to Microsoft Office.Familiar user interface makes macro development and migration intuitive.
microsoft office alternative - wps office

Frequently Asked Questions

Why does the X button still close the workbook when macros are disabled?

VBA event handlers like Workbook_BeforeClose only execute if macros are enabled in the application's Trust Center. If macros are disabled, the security restrictions in your code are bypassed entirely.

Can I disable the X button on the UserForm itself?

Yes, you can prevent users from closing the UserForm with its own X button by intercepting the UserForm_QueryClose event. Inside the event, write 'If CloseMode = vbFormControlMenu Then Cancel = True'.

How do I force users to enable macros to enforce this closure rule?

A common VBA workaround is to hide all functional worksheets except a 'Warning' sheet before saving. Use the Workbook_Open event to unhide the functional sheets. If macros are disabled, the user only sees the Warning sheet and cannot interact with the workbook.

Does this Public variable method work in WPS Spreadsheet?

Yes, WPS Spreadsheet natively supports VBA and UserForms. The Public variable declaration and Workbook_BeforeClose event logic function identically to Microsoft Excel.