VBA UserForms are a key tool for managing user interactions. When UserForms are well designed, they guide users through the options and settings without any help file or guidance. However, from my own UserForm development, I know one of the most overlooked aspects is how to close VBA UserForms. Is it best to hide or unload the form? Does it matter either way?
This post covers key considerations to ensure the closure process achieves the desired outcome.
Table of Contents
Download the example file: Join the free Insiders Program and gain access to the example file used for this post.
File name: 0123 Close VBA UserForms.zip
Basic VBA code
To start, let’s look at the basic VBA code for opening and closing UserForms. In this post, the UserForm is called myUserForm, which looks like this.
The UserForm has three elements:
- Text box: named txtTextBox
- Hide button: named cmdHide
- Unload button: named cmdUnload
Display a UserForm
The code to display a UserForm is usually stored outside of the UserForm in a standard module. The following code displays a UserForm.
Sub OpenUserForm()
'Show a userform
myUserForm.Show
End Sub
Hide a UserForm
To hide a UserForm using a button, the code may be contained within the UserForm itself, or in a separate module.
Code contained within the UserForm
The code below shows an example where the code is contained within the UserForm. Me. refers to the UserForm object.
Private Sub cmdHide_Click()
'Hide the UserForm using code within the UserFrom module
Me.Hide
End Sub
Code contained within a standard code module
Where the code is contained within another module, we must refer to the name of the UserForm, as shown by the code below.
Sub hideMyForm() 'Hide the UserForm using code within a standard module myUserForm.Hide End Sub
When using the hide code outside of the UserForm, we need to call the code from the button.
Private Sub cmdHide_Click()
'Call the code contained within a standard module
Call hideMyForm
End Sub
Unload a UserForm
Another way to close a UserForm is to unload it. Like the examples above, the code can be referenced from within or outside the UserForm code module.
Code contained within the UserForm
The code is an example where the code is contained within the UserForm.
Private Sub cmdUnload_Click()
'Unload the UserForm using code within the UserForm module
Unload Me
End Sub
Code contained within a standard code module
The example below is where the code is contained within a standard code module
Sub unloadMyForm()
'Unload the UserForm using code within a standard module
Unload myUserForm
End Sub
When using the unhide code outside of the UserForm, we need to call the code from the UserForm.
Private Sub cmdUnload_Click()
'Call the code contained within a standard module
Call unloadMyForm
End Sub
Close button
The simplest option to close a UserForm is the standard [X] close button. It is convenient because it is always at the top of the window where the user expects it. By default, it doesn’t require any code to work as it’s part of the Windows framework.
The close button is equivalent to the unload method to close the VBA UserFrom.
Assigning the Esc key
The Esc key is commonly used within interface design to close a window. We can use this on our UserForm too, though the option to apply the setting is in a strange place.
Select a button on the UserForm and set the Cancel property to True.
When the UserForm is displayed, pressing the Esc key triggers the button with the Cancel property. Therefore, the Esc key can be assigned to a button with hide or unload.
Hide vs Unload
As there is an option to hide or unload, which should we choose? Does it matter? YES, it definitely does matter.
The two options differ in their approach; they achieve slightly different things.
Unload
Unload closes the form completely; it no longer exists in memory. It will be as if the initialize event had never triggered, so if we refer to any of the objects on the UserForm, they will have no value.
Hide
Hide makes a UserForm invisible. It is still there; we just can’t see it. As it still exists, we can still reference objects on the form to retrieve their values.
For example, if there is text in a text box, we can obtain that value after the form has closed. The code below displays a message box with the text from the UserForm.
Sub displayTextFromClosedUserForm()
'Display the text contained within the UserFrom text box
'If UserFrom not loaded, it will display nothing
'If UserForm hidden, will display the value
MsgBox myUserForm.txtTextBox
End Sub
Note: As the form remains open, it continues to hold the memory.
Preloading the UserForm
What if we want to reference an object on a UserForm before we display it? The initialize event has not been executed, and the form does not exist in memory. To get around this issue, we use the load command to create the UserForm, but not display it.
Sub LoadUserForm()
'Load the UserForm without displaying
Load myUserForm
End Sub
By using this method, the initialize event triggers, but not the activate event. The activate event only triggers only when the UserForm is displayed.
To guarantee the UserForm is always in memory, load the form during the workbook open event.
How to close using the X button
By default, the [X] close button unloads the UserForm. Which is a problem if we want to hide it. But we can hijack the [X] button control to achieve the same as the hide command.
The UserForm has an event called QueryClose. Using this event, we can cancel the unload action, then hide the UserForm.
Enter the following code in the UserFrom code module
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
'Capture the [X] button click
If CloseMode = vbFormControlMenu Then
'Stop the default unload close
Cancel = True
'Force the Hide close
Me.Hide
End If
End Sub
Once we’ve canceled the unload action, we can now refer to the elements on the UserForm, just like the normal hide.
How did the user close the form?
As there are multiple ways to close a UserForm, it may be helpful to know which option the user selected. For example, did they click “OK” or “Cancel”?
In this situation, an easy option is to use a public variable. We can assign the value to the variable as part of the close procedure.
The public variable can then be accessed by any module.
Related Posts:
- Resize a UserForm with VBA or Windows API
- Hide or disable a VBA UserForm [X] close button
- Private vs Public Subs, Variables & Functions in VBA
Discover how you can automate your work with our Excel courses and tools.
Excel Academy
The complete program for saving time by automating Excel.
Excel Automation Secrets
Discover the 7-step framework for automating Excel.
Office Scripts: Automate Excel Everywhere
Start using Office Scripts and Power Automate to automate Excel in new ways.