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.
Download the example file: Click the link below to download the example file used for this post:
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
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 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 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.
- 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
About the author
Hey, I’m Mark, and I run Excel Off The Grid.
My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.
In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).
Do you need help adapting this post to your needs?
I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.
But, if you're still struggling you should:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise. List all the things you've tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
Don't go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts: