Yes—Excel VBA can stop a workbook from closing when someone clicks its X by handling the Workbook_BeforeClose event and setting Cancel to True. For a VBA UserForm, use its QueryClose event instead; neither approach physically removes the X, but each can block the close action.
First identify which X you mean
Excel has more than one close control. A workbook window can have its own close button, and the Excel application window has a separate one. A UserForm—a custom dialog created in the VBA editor—has another. The right event depends on what is being closed.
For a workbook, Workbook_BeforeClose is the normal VBA route. It runs when Excel is about to close that workbook, whether the request comes from a workbook close button or from closing Excel. For a form, UserForm_QueryClose can distinguish a user choosing the form’s control-menu close action from code unloading the form. These events intercept a close request; they do not change the appearance or remove the button.
Excel for Microsoft 365 on Windows is the main focus here. Excel for Mac also supports these VBA events, although its window controls look different. Excel for the web cannot run VBA macros, so these event-based protections do not operate there.
Block a workbook from closing
Put the event procedure in the ThisWorkbook code module, not in a worksheet module or an ordinary module. In Excel, press Alt+F11 to open the Visual Basic Editor, find the workbook in Project Explorer, and double-click ThisWorkbook. From the two drop-down lists above the code pane, choose Workbook and then BeforeClose. Excel creates the event shell; add the message and cancellation line inside it.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
' Stop the workbook from closing through Excel's normal close command.
Cancel = True
MsgBox "This workbook must remain open.", vbInformation
End Sub
Before running a macro, save a copy of the workbook: macro actions cannot be undone with Ctrl+Z. Save the working file as an Excel Macro-Enabled Workbook (.xlsm) so the VBA project is retained. If you save as .xlsx, Excel removes the VBA project.
With the event active and macros permitted, clicking a close control that would close this workbook triggers the message and leaves it open. The code blocks every normal close attempt, not just the mouse click on X. That includes commands such as File > Close and keyboard close commands when they target the workbook. It also means the user cannot use an ordinary close action to exit the workbook until the event’s behavior is changed or the macro is disabled.
Let users close it in an approved way
A workbook that can never be closed normally is often frustrating, and users may lose work if they resort to ending Excel. A better design is to explain why the workbook must stay open and provide a deliberate exit route. For example, a custom “Finish” button can run a procedure that confirms whether to save, then allows the workbook to close.
That exit procedure needs to coordinate with Workbook_BeforeClose. A common pattern is to keep a Boolean permission flag in a standard module, set it only in the approved exit procedure, and have the event cancel the close only when the flag is not set. Decide explicitly how the exit procedure handles unsaved changes, read-only files, and a user who cancels a save dialog. Avoid setting a permanent “allow close” flag and assuming it will reset itself: if the user cancels the later save prompt, the workbook may remain open with the permission still enabled.
For a simple workbook, another option is to show a message in the event that tells the user which supported exit command to use, then implement that command as a macro. Keep the instructions visible and test both the save and discard paths on a copy. A close-blocking event should not be used to conceal a workbook’s behavior or to prevent a legitimate owner from closing their file.
Stop the X from closing a UserForm
If the X is on a VBA UserForm rather than an Excel window, put this event procedure in that form’s code module. In the Visual Basic Editor, open the form’s code pane, select UserForm in the left drop-down, and choose QueryClose in the right one.
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
' Cancel a close request made through the form's control menu.
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Use the Continue button to leave this form.", vbInformation
End If
End Sub
The CloseMode test matters: it targets a close initiated through the form’s control menu rather than refusing every possible way to unload the form. A button on the form can still run Unload Me when the form is meant to close. You can make that button perform a validation step first, then unload only if the user is allowed to continue. Test the form’s X, its button, and any code that opens or unloads it so the behavior is deliberate.
This is useful for a required data-entry step, but it should not become a trap. Give the user a clear route to cancel or finish, and explain what happens to entered data. If you block the form’s close request without providing another way out, the user may have to end the macro or Excel rather than complete the task.
What the code does—and does not do
Setting the event’s Cancel argument prevents Excel from completing the close operation. It does not disable the operating system’s window control, remove the X, or make the workbook impossible to terminate. The distinction is important: VBA is enforcing an application-level rule while its code is running, not changing Windows or Excel into a locked-down environment.
Users with access to the file can disable macros, open it with macros blocked, or use other means to end Excel. A crash or forced termination can also close the program without the intended event behavior. Do not rely on Workbook_BeforeClose as a security boundary, a licensing control, or a guarantee that data will be saved. Use file permissions and appropriate data-storage controls for access, and use routine backups to reduce the impact of accidental loss.
Windows API techniques that alter a window’s system menu or close-button state are a different approach. They are more complex, can behave differently across Excel versions and window arrangements, and may require separate declarations for 32-bit and 64-bit Office. Such code is not a good default for an ordinary workbook, and Windows-specific API calls are not portable to Mac. If the actual requirement is simply “warn before closing” or “save before leaving,” an event procedure is usually safer than modifying the window itself.
Common problems and checks
If the event seems not to run, check the code location and the macro environment before changing the procedure. Event procedures are tied to their workbook or form; a correctly named procedure placed in a standard module will not act as that object’s event handler.
- Wrong module: Workbook-close code belongs in
ThisWorkbook. Form-close code belongs in the relevant UserForm’s code module. - Macros are disabled: The workbook event cannot run when the VBA project is not allowed to run. Use a trusted, properly managed file rather than asking users to lower security settings indiscriminately.
- The file was saved as .xlsx: That format does not retain VBA code. Use
.xlsmfor a macro-enabled workbook. - More than one workbook is open: Excel may remain open after one workbook closes because other workbooks are still open. Conversely, the application’s close command can attempt to close the active workbook as part of exiting Excel.
- The code is trapping every close: A procedure that always sets
Cancel = Truealso blocks legitimate close commands. Add a clear, tested exit path instead of relying on users to disable the macro.
When testing, use a disposable copy and try the workbook-window X, the Excel application X, and the form’s X separately. Also test with unsaved changes and with macros enabled. The exact prompts a user sees can depend on whether the workbook is dirty, read-only, or part of a multi-workbook Excel session.
When to choose another approach
If the goal is to prevent accidental data loss, blocking the X is often more restrictive than necessary. A save reminder, a confirmation prompt, or automatic saving may address the actual problem with less disruption. For example, a workbook can use a close event to ask whether the user intended to exit and cancel only if the user chooses to stay. Another design is to validate required fields when the user presses a clearly labeled submit or finish button, rather than preventing Excel from closing at all.
If the workbook is a kiosk-style interface, consider whether a purpose-built application or managed environment is more appropriate. Excel macros can improve a workflow, but they do not make a workbook tamper-proof. On Mac, prefer the built-in VBA event approach over Windows API code; on Excel for the web, use a web-compatible workflow because VBA does not run there.
Frequently Asked Questions
Can VBA remove the X from an Excel workbook window?
The standard workbook event approach does not remove the button; it cancels the close request. Removing or changing a window control requires a separate, platform-specific technique and is generally more fragile than handling the close event.
Does Workbook_BeforeClose catch Alt+F4?
When Alt+F4 initiates a normal close of the workbook or Excel session, Excel’s workbook close event is the relevant place to cancel the workbook close. The event handles the close operation, not a specific keyboard key, and it cannot prevent a forced termination of Excel.
Will this work if the workbook is opened in Excel for the web?
No. Excel for the web does not execute VBA macros, so VBA close-event code cannot block a close there. Use desktop Excel for this macro behavior or design a workflow that does not depend on VBA.
Can I block the X on a UserForm but still close it with a button?
Yes. Cancel the form’s control-menu close request in UserForm_QueryClose, then let the button run your chosen validation and Unload Me when closing is permitted.
Does the close event save my changes?
No. Setting Cancel = True stops the close; it does not save the workbook. Handle saving separately and test the prompts and outcomes, especially if the file is read-only or the user cancels a save operation.
Related
- Excel To-Do List Templates: How to Choose and Use One
- Excel Meal Prep Template: Plan Meals, Shop Smarter
- Excel Template for Quotation Comparison: Build One
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA MsgBox Cancel: Buttons, Return Values, and CodeTo detect Cancel in VBA, show a MsgBox button set that includes Cancel, store the function’s result, and…
- VBA Loop Without Do: Choosing For, While, or For EachYes. In VBA, you can loop without using Do: use For...Next for a known number of repetitions, For…
- Microsoft Excel VBA InputBox: How to Use It SafelyIn Excel VBA, use InputBox to ask a person for a text response, or use Application.InputBox when you…
- Excel VBA Insert Image: Code and OptionsTo insert an image with Excel VBA, call the worksheet’s Shapes.AddPicture method, supplying the image file path and…
- Using MsgBox in VBA: Syntax, Buttons, and Return ValuesIn Excel VBA, use MsgBox to show a pop-up message or ask a user a simple question. Call…
- Mouse Macro to Toggle a Laser While Aiming in GamesYes—a mouse macro can usually send the keyboard or mouse input that toggles an in-game weapon laser, if…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.