This post may contain affiliate links. Please read my disclosure for more info.

Closing VBA UserForms: Hide vs Unload

Closing VBA Form Hide vs Unhide

VBA UserForms are a key tool for managing user interactions.  When a UserForm is well designed, it guides the user through the options and settings without any help file or guidance.  From my own UserForm development, I know one of the most overlooked aspects is the closure of the UserForm itself.

This post will cover some of the key considerations to ensure the closure process achieves the desired outcome.

Basic VBA code

To start with, let’s look at the basic VBA code for opening and closing UserForms.  In this post, the UserForm is called myUserForm, and it looks like this.

myUserForm

The button names are cmdHide and cmdUnload, and the text box is called txtTextBox.

Display a UserForm

The code to display a UserForm is usually stored outside of the UserForm itself, in a standard module.  The following code will show the UserForm.

Sub OpenUserForm()

myUserForm.Show

End Sub

Hide a UserForm

To hide a UserForm using a button, the code can be contained with the UserForm itself, or in a separate module.

The code below shows an example where the code is contained within the UserForm.  Me is used to reference the UserForm object.

Private Sub cmdHide_Click()

Me.Hide

End Sub

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()

myUserForm.Hide

End Sub

Unload a UserForm

Another way to close a UserForm is to unload it.  Just like the examples above, the code can be referenced from within or outside the UserForm code module.

Referenced from within the UserForm module:

Private Sub cmdUnload_Click()

Unload Me

End Sub

Referenced from outside the UserForm module:

Sub unloadMyForm()

Unload myUserForm

End Sub

Close button

The simplest option to close a UserForm is the standard close button.  It is convenient as it right there at the top of the window. By default, It doesn’t require any code to work as it’s part of the Windows framework.

Close Button

Be aware that the close button will use the unload method when closing the UserForm.

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.

Set Cancel Key

When the UserForm is displayed, pressing the Esc key will trigger the button you applied the Cancel property to.  Therefore Cancel can be assigned to a button with hide or unload.

Hide vs Unload

As there is an option to hide or unload, which should you choose?  Does it matter? YES, it definitely does matter.

The two options are different in their approach because they achieve slightly different things.

Unload

Unload will close the form completely; it will no longer exist in memory.  It will be as if the initialize event has not 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’s still there, we just can’t see it.  As it still exists, we can still reference the objects on the form to retrieve their values.

As an example, if there is text in a text box, we can still reference it after the form has closed.  The code below will display a message box with the text from the UserForm after the form has been closed.

Sub hideMyFormShowMessage()

myUserForm.Hide

MsgBox myUserForm.txtTextBox

End Sub

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 executed and the form does not exist in memory.  To get around this issue, we can use the load command to create the UserFrom, but not display it.

Sub LoadUserForm()

Load myUserForm

End Sub

By using this method, the initialize event will trigger, but not the activate event.  The activate event will trigger only once 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 will unload 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 Button

The UserForm has an event called QueryClose.  Using this event, we can cancel the unload action, then hide the UserForm.

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

If CloseMode = vbFormControlMenu Then

    Cancel = True
    Me.Hide

End If

End Sub

Once you’ve canceled the unload action, you could 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 useful to know which option the user selected.  Did they click “OK” or “Cancel”?  In this situation, it is easier to use a public variable.  Assign the value to the variable as part of the close procedure.

The public variable can then be accessed by any module.



Want to Learn VBA & Macros?

If you want to automate Excel and save time, my recommended resouces for learning VBA Macros are:

Leave a Reply

Your email address will not be published. Required fields are marked *