You have two ways to close a UserForm. One way is with the Unload method and the other with the Hide method. Although both methods make the UserForm appear to disappear, they each perform different actions. This can be a point of confusion for beginner programmers, so it’s important to understand the distinction between Unload and Hide.
Unloading a UserForm
When you unload a UserForm, the form closes and its contents are cleared from memory. In most cases, this is what you want: that the entered data is saved in some way or passed to public variables, and then the form is closed. The instruction that unloads a UserForm is simply Unload Me, and it is usually associated with a CommandButton for that purpose—such as the Cancel button placed on the example UserForm in this lesson.
Suppose you want to unload the UserForm when you click the Cancel button. A quick and easy way to do this is to double-click the command button in the UserForm design view, as shown in Figure.
When you double-click the command button, you see these lines of code in the UserForm module:
Private Sub CommandButton2_Click() End Sub
To complete the Click procedure, type Unload Me. When you click the Cancel button, the UserForm is unloaded—that is, it closes and clears from memory the data that had been entered—using this Click event for that button:
Private Sub CommandButton2_Click()
Unload Me
End Sub
NOTE
The keyword Me refers to the name of the active window.
Hiding a UserForm
The Hide method makes the UserForm invisible, but the data that was in the UserForm remains in memory and can be displayed again when the form is shown once more. In some situations, this is exactly what you want—for example, when you are interacting with two or more UserForms and you want the user to focus on only one form at a time.
The instruction to hide a UserForm is:
Me.Hide