Microsoft Office Tutorials and References
In Depth Information
Closing a UserForm
Event-handler procedures must be located in the Code window for the UserForm.
However, your event-handler procedure can call another procedure that’s located in a
standard VBA module.
Your VBA code can change the properties of the controls while the UserForm is displayed (that
is, at runtime). For example, you could assign to a ListBox control a procedure that changes
the text in a Label when an item is selected. This type of manipulation will become clearer later in
Closing a UserForm
To close a UserForm, use the Unload command, as shown in this example:
Or, if the code is located in the code module for the UserForm, you can use the following:
In this case, the keyword Me refers to the UserForm. Using Me rather than the UserForm’s name
eliminates the need to modify your code if you change the name of the UserForm.
Normally, your VBA code should include the Unload command after the UserForm has
performed its actions. For example, your UserForm may have a CommandButton that serves as an
OK button. Clicking this button executes a macro. One of the statements in the macro will unload
the UserForm. The UserForm remains visible on the screen until the macro that contains the
Unload statement finishes.
When a UserForm is unloaded, its controls are reset to their original values. In other words, your
code won’t be able to access the user’s choices after the UserForm is unloaded. If the user’s
choice must be used later on (after the UserForm is unloaded), you need to store the value in a
Public variable, declared in a standard VBA module. Or you could store the value in a
worksheet cell, or even in the Windows registry.
A UserForm is automatically unloaded when the user clicks the Close button (the X in
the UserForm title bar). This action also triggers a UserForm QueryClose event,
followed by a UserForm Terminate event.