How to Allow a UserForm to Close an Excel Workbook While Disabling the X Button
Question details
The user wants to allow closing an Excel workbook exclusively via a VBA UserForm, disabling the standard application close (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.
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.
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.
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'.
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'.
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.
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.

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. Open your Macro Workbook: Launch WPS Office and open your .xlsm file directly in WPS Spreadsheet.
- 2. Access the Developer Tab: Navigate to the 'Developer' tab on the top ribbon to access all macro tools.
- 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.

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.




