Closing a User Form

To close a UserForm, use the Unload command. For example:

Unload UserForml

Or, if the code is located in the code module for the UserForm, you can use the following:

Unload Me

In this case, the keyword Me refers to 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 will not 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.

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.

UserForms also have a Hide method. When you invoke this method, the UserForm disappears, but it remains loaded in memory, so your code can still access the various properties of the controls. Here's an example of a statement that hides a UserForm:

UserForm1.Hide

Or, if the code is in the code module for the UserForm, you can use the following:

Me.Hide

If for some reason you would like your UserForm to disappear immediately while its macro is executing, use the Hide method at the top of the procedure and follow it with a DoEvents command. For example, in the following procedure, the UserForm disappears immediately when CommandButton1 is clicked. The last statement in the procedure unloads the UserForm.

Private Sub CommandButton1_Click() Me.Hide DoEvents

Next r Unload Me End Sub

In Chapter 15, I describe how to display a progress indicator, which takes advantage of the fact that a UserForm remains visible while the macro executes.

Was this article helpful?

0 0

Responses

  • hildigrim
    How to code close a userform in vba?
    9 years ago
  • katlyn
    How to get a vba userform to disappear after performing its function?
    7 years ago

Post a comment