To hide an Excel VBA UserForm without unloading it, call Me.Hide from code inside the form. The form disappears but stays loaded, so its controls and values are still available when you show it again; use Unload Me instead when you want to close and discard that instance.
What Hiding a UserForm Does
A UserForm is a custom window for gathering input or presenting controls such as text boxes, buttons, and lists. Hiding changes whether the window is visible; it does not close the form or clear its controls. In practice, this is useful when a person should temporarily return to the worksheet and later continue where they left off.
The distinction matters because a hidden form remains loaded in memory. Its text box values, selected options, and other control state usually remain as they were. Hiding also returns control to the VBA procedure that displayed the form, which lets that procedure inspect the form’s values or carry out other work.
For example, a form can collect a filter choice, hide while the user reviews the sheet, and appear again with that choice still selected. Hiding is not the same as minimizing the form, and it does not make the form visible in the background. It simply removes the window from view while leaving the instance alive.
Hide a Form from One of Its Buttons
In the Visual Basic Editor, open the UserForm, double-click its Hide or Cancel button, and put the hide command in that button’s Click event. The keyword Me refers to the current instance of the form, so this approach continues to work if your code creates more than one instance.
Private Sub cmdHide_Click()
' Hide this instance; keep its controls and values.
Me.Hide
End Sub
Replace cmdHide with the actual button name. To view or edit the code in Excel for Microsoft 365 on Windows, use Developer > Visual Basic, then open the form in the Project Explorer. In the Visual Basic Editor, use Insert > UserForm to create a form if you do not already have one. On Mac, the Visual Basic Editor is also available from the Developer tab; Option+F11 is the common keyboard shortcut, though some keyboards require Fn as well. Excel for the web does not run VBA macros.
Before running a macro, save a copy of the workbook: VBA actions cannot be undone with Ctrl+Z. Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm), and only enable macros in files from sources you trust.
Show the Form Again
If you displayed the built-in default instance, you can show that same instance again with its name. For a form named UserForm1, the following procedure can be placed in a standard module and assigned to a worksheet button or run from the Macro dialog:
Public Sub ReopenEntryForm()
' Show the default instance again.
UserForm1.Show
End Sub
If the form was hidden rather than unloaded, showing it again reveals the existing instance, including its retained control values. By contrast, unloading the default instance and then showing it again creates a fresh form state. If you want a form to start empty on every visit, unloading it is often the simpler choice; if you want to preserve a partially completed entry, hide it.
For a modal form, .Show pauses the calling procedure until the form is hidden or unloaded. When the form hides, the procedure resumes, and it can read values from the still-loaded form. A modeless form does not pause the caller, so the worksheet and form can both be used while the form is open. To show a form modelessly, use UserForm1.Show vbModeless; choose modeless behavior only when simultaneous interaction is useful.
Hide Versus Unload
The two commands solve different problems. Me.Hide preserves the current form instance; Unload Me closes and unloads it. The table summarizes the practical choice.
- Use Hide when the user may return to the same form and you need its current control values to remain available.
- Use Unload Me when the form is finished, should release its loaded instance, or should be reset before it is shown again.
- Use Hide, then read values when a modal form gathers information that the calling procedure will process after the user clicks a button.
Unloading a form does not automatically save its data somewhere else. If the values matter after unloading, copy them to worksheet cells, variables, or another data structure before you unload. Similarly, hiding a form is not a substitute for saving workbook changes: it only affects the form’s visibility and state.
These examples show the commands in their usual locations:
Private Sub cmdCancel_Click()
' Keep the current form available for another visit.
Me.Hide
End Sub
Private Sub cmdFinish_Click()
' Close this instance when the task is complete.
Unload Me
End Sub
A Cancel button does not have to unload the form. Decide whether cancel means pause and return later, or abandon the current entry. If abandoning it, unload the form or clear the controls deliberately; otherwise the next display may show the values from the canceled attempt.
Default Instances and Separate Form Instances
When you refer to UserForm1.Show by its form name, VBA uses the form’s default instance. This is convenient for a single form and is common in introductory examples. Its state can persist after UserForm1.Hide because the default instance remains loaded.
Code can also create a separate instance with New UserForm1. That instance is not necessarily the same object as the default instance. If you hide a separately created form and later call UserForm1.Show, you may see a different instance rather than the one you hid. Keep and reuse a reference to the particular instance when you need that behavior. This distinction is especially important for modeless forms, where the procedure that creates the form may finish while the form is still on screen.
For a simple workbook, use one default instance consistently. For workflows that require multiple forms or independent sets of input, create explicit instances and manage their references intentionally. Avoid mixing instance styles without a reason, since it can make it unclear which form contains the user’s latest choices.
What Happens When Someone Clicks the X
Clicking the title-bar close button normally closes the form rather than hiding it. If you want the X to behave like a temporary hide, handle the form’s QueryClose event and cancel the close when the close mode indicates the form control menu. The event provides the required arguments:
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
' Convert the title-bar X action into a hide.
If CloseMode = vbFormControlMenu Then
Cancel = True
Me.Hide
End If
End Sub
This changes the behavior for the title-bar X only. A separate button can still unload the form, and other close paths need to be handled according to the intended workflow. Consider whether users will understand that the X merely hides the window: a hidden form can remain loaded even though it is no longer visible. For a straightforward dialog, allowing the X to close the form can be less surprising.
Common Problems and Practical Checks
- The form vanishes and does not return. Check whether the code uses
Unloadrather thanHide, or whether it shows a different form instance when reopening. - The form returns with old entries. That is expected after hiding. Clear specific controls when appropriate, or unload the form when a fresh state is required.
Mecauses an error.Meworks inside a class module, including the code module for that UserForm. It is not a reference you can use in an ordinary standard module. From a standard module, refer to the correct form instance instead.- The worksheet remains blocked. A form shown modally blocks interaction with Excel until it is hidden or unloaded. Use
vbModelessif the user must work in the worksheet while the form is open. - Values disappear after closing. Hiding retains them temporarily, but unloading ends that instance. Copy important data somewhere durable before unloading.
When debugging, place a breakpoint on the hide or unload line and step through the caller after the form closes. This helps establish whether the procedure resumes as expected and whether it is reading the instance that actually collected the input. Also check the form name in the Project Explorer rather than assuming its displayed caption is its VBA name; captions and code names can differ.
Frequently Asked Questions
How do I hide a UserForm in Excel VBA? Put Me.Hide in an event procedure inside the form, such as a button’s Click event. From outside the form, use the reference to the correct instance, such as UserForm1.Hide for its default instance.
Does hiding a VBA UserForm reset its text boxes? No. Hiding leaves the form loaded, so its controls generally retain their current values. Clear the controls yourself or unload the form if the next display should start fresh.
How do I close a UserForm instead of hiding it? Use Unload Me inside the form’s code. Before unloading, copy any values you still need to a worksheet or other storage, because the unloaded instance is no longer available to read.
Can I show a hidden UserForm again? Yes. Show the same instance again. For a form displayed through its default instance, call UserForm1.Show; if you created a separate instance, keep and use a reference to that instance.
Can a hidden UserForm still run code? Yes. Hiding only affects visibility. A procedure can continue after a modal form is hidden, and other VBA code can access a loaded form’s properties. Avoid leaving hidden forms loaded unnecessarily, especially if users might mistake them for closed forms.
Related
- Protect Sheet in Excel: Allow Editing in Certain Cells
- Jira Merge Table Cells: Steps and Limitations
- Conditional Formatting: Highlight Duplicates in Excel
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA: Copy an Entire Worksheet into an Array SafelyIn Excel VBA, assign a worksheet range’s Value2 property to a Variant to copy its cell values into…
- Excel Macro Range: How to Reference and Use RangesAn Excel macro range is one cell or a group of cells that VBA can read, change, format,…
- Excel VBA Date and Time Variable TypesIn Excel VBA, use the Date data type for a calendar date, a time, or a date-and-time value;…
- VBA MsgBox Yes/No: Continue If the User Clicks YesUse VBA’s MsgBox with the vbYesNo button option, save the result, and put the work that should continue…
- VBA MsgBox Command: Syntax, Buttons, and Examples in ExcelIn VBA, MsgBox displays a message in a dialog box and can return a value indicating which button…
- VBA Import CSV in Excel: Methods, Code, and FixesTo import a CSV file with VBA in Excel for Microsoft 365 on Windows, use Workbooks.OpenText to parse…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.