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.
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
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.
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.
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 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 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.
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.