Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook


How to close VBA UserForm: Hide vs Unload

Close VBA UserForm

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.

Close VBA UserForm example objects

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.

Close VBA UserForm X button

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.

Esc key to close VBA UserForm

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.

Close VBA UserForm X button

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:


Headshot Round

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:

  1. Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
  2. Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
  3. 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.
  4. Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.

What next?
Don't go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

Leave a Reply

Your email address will not be published.