To make an Excel VBA UserForm button act as Cancel, set that CommandButton’s Cancel property to True, then put the closing or cleanup action in its Click event. When the form is active, pressing Esc activates that button; the property does not automatically undo worksheet changes or close the form without event code.
What the Cancel property does
A UserForm’s Cancel button provides a keyboard shortcut for the same action as clicking the button. In Microsoft Forms, a CommandButton with Cancel = True is activated by Esc while its form is active. Its normal Click event runs, so you can use one handler for both mouse and keyboard cancellation.
This is different from a universal “discard changes” command. Excel does not infer which values should be restored, and the Cancel property does not reverse code that has already run. You decide what cancellation means: close an unchanged form, discard values still held in controls, restore previously saved values, or run other cleanup.
Usually, cancellation should leave the workbook as it was before the dialog opened. The simplest way to achieve that is to keep proposed edits in the form’s controls until the user chooses Save or OK. The Cancel button can then close the form without writing those proposed values to cells.
Set the button’s Cancel property
In the Visual Basic Editor on Windows, select the UserForm in the Project Explorer and add a CommandButton from the Toolbox if needed. Select the button, open the Properties window, and set its (Name) to something descriptive, such as cmdCancel. Set the Cancel property to True. The button’s caption can say Cancel; the caption is just visible text, while the property controls Esc behavior.
You can set the property in the Properties window at design time, or in code when the form initializes. Design-time setup is usually easiest to inspect. Check that only the intended button is designated as the Cancel button, and give it an event procedure that closes the form or performs the cleanup you need.
For example, press Alt+F11 to open the Visual Basic Editor on Windows, then double-click the form or its button to create the relevant event procedure. On a Mac, the editor shortcut is commonly Option+F11; some keyboards require Fn as well. The editor layout and available controls can differ between platforms.
Write the button’s Click event
For a form that has not written any changes to the worksheet yet, unloading it is a straightforward cancel action. The following example assumes that the form is named UserForm1 and its cancel button is named cmdCancel. Set the button’s Cancel property to True in the Properties window, or use the initialization procedure shown here.
Before running any macro, save a copy of the workbook: VBA macros cannot be undone with Ctrl+Z. Save a macro-enabled workbook as .xlsm or .xlsb if it needs to retain its VBA code; the .xlsx format does not store VBA macros.
Private Sub UserForm_Initialize()
' Let Esc activate the cancel button.
Me.cmdCancel.Cancel = True
End Sub
Private Sub cmdCancel_Click()
' Close the form without committing pending control values.
Unload Me
End Sub
Here, clicking Cancel and pressing Esc both run cmdCancel_Click. If the property was already set in the Properties window, the initialization procedure is optional. Replace the example control and form names with the names used in your project.
Choose between Unload and Hide
Unload Me closes the current form and removes that form instance from memory. When the form is shown again, it is initialized again, so values typed into its controls are generally not retained. This is a natural choice when Cancel means “discard this form session and close.”
Me.Hide makes the form invisible but keeps the instance loaded. That can be useful when code that opened the form must inspect a result or the values in its controls after the form closes. However, hidden controls still contain the user’s entries. If the form is reopened without being reset, those old entries may appear again.
Neither method rolls back changes already made to cells, files, or other objects. If the form writes to a worksheet as the user types, unloading it will not restore the original contents. In that situation, either postpone writing until the user confirms, or deliberately save and restore the original values as part of your form’s design.
Keep cancellation separate from saving
A dependable dialog follows a simple transaction pattern: load the current values into controls, let the user edit them, validate the proposed values when they choose Save or OK, and only then write them to the workbook. Cancel closes the dialog without committing. This makes cancellation predictable and avoids needing to reverse a series of cell edits.
For example, a form for changing a customer’s name and region can display the existing values in text boxes and a combo box. The Save button can check that required entries are present, then update the relevant cells. The Cancel button should not call that saving routine. If users can edit cells directly while a modeless form is open, consider how those external edits interact with the form’s original values before applying a save.
If your program needs to know whether the user chose Save or Cancel, record that outcome explicitly rather than guessing from whether the form is visible. One approach is to set a form-level result variable in each button’s event and hide the form, let the calling code read the result, and then unload the form. The important detail is to read the result before unloading or reinitializing the instance, and to reset it each time a new dialog session begins.
Modal forms and how the calling code continues
A modal form, shown with vbModal, prevents the user from interacting with the rest of Excel until the form is hidden or unloaded. It is often a good fit for a short data-entry dialog because the calling procedure waits at the Show statement while the user makes a choice. When the Cancel event unloads the form, execution continues after that statement.
A modeless form, shown with vbModeless, allows the user to work elsewhere in Excel while the form remains open. In that case, the procedure that displays the form does not wait for the user to finish. Code that needs to respond to the eventual choice should therefore run from the form’s button events or another suitable event, rather than immediately after the Show statement.
For a basic modal dialog, a standard module can show the form with a procedure such as UserForm1.Show vbModal. The actual form name may differ. Decide whether you need a modal or modeless form before building the result-handling logic, because it changes when the calling procedure can safely inspect the user’s choice.
Cancel is not the same as Default
CommandButtons also have a Default property. A button with Default = True is the form’s default button and is normally activated by Enter when the form has focus. The Cancel property assigns the Esc behavior. A Save or OK button is often the default, while a separate Cancel button handles Esc.
These properties do not replace input validation. If Enter activates Save, the Save event still needs to check that the entered values are valid before changing the workbook. Likewise, Esc activating Cancel only runs the cancel button’s event; it does not make an unsafe Save routine safe or automatically discard changes that were already committed.
Common problems and fixes
- Esc does nothing: Confirm that the correct CommandButton has
Cancel = True, that the form is active, and that the button has a working Click event. A button captioned Cancel is not enough by itself. - The button closes the form but cells still changed: Look for code that writes to cells before the user confirms. Move those writes into the Save or OK event, or implement an explicit restoration process.
- The form reopens with old entries: This is expected if the form was hidden rather than unloaded. Reset the controls when opening a new session, or unload the form when its values should be discarded.
- Cancel closes the form but the calling code continues unexpectedly: That is normal for a modal form after it is hidden or unloaded. Add an explicit result value if the caller must take different actions for Save and Cancel.
- Another button responds to Enter: Check which CommandButton has
Default = True. Enter and Esc use separate properties, so changing one does not necessarily change the other.
For debugging, put a breakpoint in the cancel button’s Click event and press Esc with the form active. If execution reaches that event, the keyboard routing works and the remaining issue is likely in the event code or in earlier worksheet changes. If it does not, inspect the button’s property and confirm that the form is the active window.
Windows, Mac, and workbook compatibility
The VBA event pattern is the same idea across Excel desktop editions, but UserForm support and available controls can differ, especially on Mac. Excel for the web does not run VBA macros or display VBA UserForms. A workbook that depends on a UserForm therefore needs to be opened in a desktop version of Excel that supports its VBA project.
When sharing a workbook between Windows and Mac, test the form and its controls on each platform you intend to support. ActiveX controls are a Windows-specific issue and should not be confused with the MSForms controls commonly used on VBA UserForms. Keyboard shortcuts also vary: Alt+F11 is the usual Windows editor shortcut, while Mac users commonly use Option+F11, sometimes with Fn depending on their keyboard settings.
Frequently Asked Questions
How do I make Esc close an Excel VBA UserForm?
Set the Cancel property of a CommandButton on the form to True, then put Unload Me or another cancel action in that button’s Click event. Esc activates the event when the form is active.
Does setting Cancel to True automatically undo worksheet edits?
No. It routes Esc to the button’s Click event. It does not reverse changes already made to cells or other objects. Keep proposed edits in form controls until Save or OK if you want Cancel to leave the sheet untouched.
Should a Cancel button use Hide or Unload?
Use Unload when the form should close and its current control values should be discarded. Use Hide when the calling code needs to inspect the form’s values or a recorded result afterward; unload it when that information is no longer needed.
Can I make Enter activate OK and Esc activate Cancel?
Yes. Set the OK button’s Default property to True and the Cancel button’s Cancel property to True. Keep validation in the OK button’s Click event so Enter cannot save invalid input.
Will a VBA UserForm work in Excel for the web?
No. Excel for the web does not run VBA macros or VBA UserForms. Use desktop Excel for a workbook that depends on this dialog, or consider a different interface if browser access is required.
Related
- Data Validation Multiple Selection in Excel: What Works
- Data Validation Interview Questions and Strong Answers
- Excel Data Validation Guidelines for Reliable Spreadsheets
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Copy and Paste Values Only in ExcelTo copy a range in Excel VBA and paste only its values, use Copy followed by PasteSpecial Paste:=xlPasteValues.…
- VBA InputBox New Line: Add Line Breaks to PromptsTo put a new line in a VBA InputBox prompt, join the prompt text with vbCrLf or vbNewLine.…
- VBA Sort Header: Keep Column Headings in Place When SortingTo keep a header row out of the sorted data in Excel VBA, set the sort option to…
- Excel VBA Copy and Paste a Row: Practical Code GuideTo copy a row with Excel VBA, specify the source and destination worksheet rows and use Copy Destination.…
- VBA InputBox Options: Types, Choices, and ExamplesVBA’s basic InputBox accepts a prompt and returns what the user types as text; it does not provide…
- Copy and Paste a Range in Excel VBA: Methods and ExamplesIn Excel VBA, copy a range with the Range.Copy method and its Destination argument; for example, identify a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.