VBA Close UserForm

What is Excel VBA Close UserForm?

In Excel VBA, a UserForm is a graphical user interface (GUI) tool that allows users to interact with the workbook by providing input or making selections. The VBA Close UserForm action refers to terminating or hiding the UserForm once its purpose has been served. It allows you to control the visibility and lifecycle of the UserForm during the execution of your VBA macro.

Let us look at an example to explore the usage of VBA Close UserForm. It is a simple UserForm that prompts the user to enter their name.

Upon clicking a button, the UserForm will let us enter any desired name, generating a personalized greeting message using the entered name.

Here, we enter the name Rahul and click on the command button, and we can see a message box that displays the text “Hello, Rahul!”

How to Close UserForm in Excel VBA?

You have multiple options depending on the desired behavior to close the VBA Close UserForm. Here are three common methods:

• Method 1: Unload Me

The “Unload Me” statement closes the UserForm and removes it from memory. This method completely unloads the UserForm and releases its resources.

Example:

Private Sub CloseButton_Click()

    Unload Me

End Sub

In this example, the CloseButton is a CommandButton control on the VBA Close UserForm. The “CloseButton_Click” event handler is triggered when the button is clicked, and the VBA Close UserForm is unloaded using “Unload Me.”

• Method 2: Me.Hide

The “Me.Hide” statement is used to hide the VBA Close UserForm while keeping it in the memory. This method allows you to show the VBA Close UserForm again without losing its state or data.

Example:

Private Sub CloseButton_Click()

    Me.Hide

End Sub

In this example, again, when the button is clicked, the “CloseButton_Click” event handler is triggered, and the VBA Close UserForm is hidden using “Me.Hide.”

• Method 3: Me.Visible = False

The “Me.Visible = False” statement is another way to hide the VBA Close UserForm while keeping it in memory. It works similarly to the “Me.Hide” method.

Example:

Private Sub CloseButton_Click()

    Me.Visible = False

End Sub

When the button is clicked, the “CloseButton_Click” event handler is triggered, and the VBA Close UserForm is made invisible using “Me.Visible = False”.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Examples

Example #1 – Close Userform Using “Unload Me” Statement in VBA

This example demonstrates how to close a VBA Close UserForm after clicking using the “Unload Me” statement while updating the worksheet with the entered data.

• Step 1: Open the Visual Basic Editor (VBE) by pressing Alt+F11.

• Step 2: Insert a UserForm. In the VBE, go to the “Insert” menu and select “UserForm” to add a new UserForm to your project. The UserForm will appear in the Project Explorer window on the left side of the VBE.

• Step 3: Design the UserForm. Double-click on the UserForm in the Project Explorer to open its code module.

Design the UserForm by adding controls such as TextBoxes, Labels, and CommandButtons as required.

In this example, make sure you have a TextBox control named “TextBox1” and a CommandButton control named “CommandButton1” on the UserForm.

• Step 4: Now right-click on the Userform and select “View Code” to open the “UserForm” code window.

Add the following code between the Sub and End Sub. Save your workbook to apply the changes. made in the VBE and close it.

Private Sub CommandButton1_Click()
‘Get the entered data from TextBox
Dim data As String
data = TextBox1.Value
‘Update the worksheet with the entered data
Sheet1.Range(“A1”).Value = data
‘Close the UserForm
Unload Me
End Sub

Private Sub UserForm_Click()

End Sub

• Step 5: To display the UserForm, go to your Excel workbook and select the “Developer” option.

In the “Developer” menu, go to Insert and select Button (Form Control).

Insert the “Button” anywhere in the Excel worksheet.

• Step 6: Now, assign the macro to this button and select the macro associated with the UserForm. To do this, right-click the button, select “Assign Macro,” and the dialog box will appear.

• Step 7: In the “Assign Macro” dialog box, click on the “New” button. The Visual Basic Editor (VBE) window will open with a new module containing the macro template. Write the macro code in the module to display the UserForm. For example:

• Step 8: Now, when you click the button, the macro will be executed, and the UserForm (named “UserForm1″ in this example) will be displayed.

• Step 9: In the displayed VBA Close UserForm, enter the desired data into the TextBox control. Click the CommandButton to update the worksheet with the entered data and close the UserForm.

Now, you will notice that the VBA Close UserForm after click is closed, and the entered data is populated in Cell A1.

• Step 10: If you click on Button 1 again, you will observe that the UserForm is reset and does not retain any data. This is because of the “Unload Me” statement used in the code, which unloads the UserForm and clears its contents.

Example #2 – Close UserForm Using Hide Method in Excel VBA

• Step 1: Open the Visual Basic Editor.

• Step 2: Insert a UserForm

Follow the steps mentioned in the previous example to create a UserForm.

• Step 3: Add Code to the UserForm

Now right-click on the Userform and select “View Code” to open the “UserForm” code window.

Add the following code between the Sub and End Sub lines.

Private Sub CommandButton1_Click()
‘Get the entered data from TextBox
Dim data As String
data = TextBox1.Value
‘Update the worksheet with the entered data
Sheet2.Range(“A1”).Value = data
‘Hide the UserForm
Me.Hide
End Sub

Private Sub UserForm_Click()

End Sub

• Step 4: Save and Close the VBE. Save your workbook to apply the changes made in the VBE.

• Step 5: To display the UserForm, follow the steps mentioned in the previous example, “Display the UserForm.”

Once you have created the Module to showcase the UserForm, the code is assigned to the button. When you click the shape or button, the UserForm will be shown on the screen.

• Step 6: In the displayed UserForm, enter the desired data into the TextBox control.

• Step 7: Click the CommandButton to update the worksheet with the entered data.

The data from the TextBox will be written to the specified worksheet and cell reference, in this case, it will be Cell A1.

• Step 8: After the data is updated in the worksheet, the UserForm will be hidden using the Me.Hide statement. The UserForm will disappear from the screen while keeping the updated data in the worksheet.

Upon clicking the command button again, you will observe that the data entered still remains in the text box. This behavior is a result of using the “Me.Hide” statement in the VBA code, which hides the UserForm but preserves its data and state.

Difference between Unload & Hide in Excel VBA

The main difference between Unload and Hide in Excel VBA lies in how they handle the UserForm:

• Unload: The Unload statement removes the UserForm from memory, freeing up system resources. It effectively terminates the UserForm and resets all its controls and variables. Once unloaded, you can only reaccess the UserForm if you reload it.

• Hide: The Hide method keeps the UserForm in memory but makes it invisible. It retains the state of controls and variables, allowing you to show the UserForm again when needed using the Show method. Hiding the UserForm is useful when removing it from view but preserving its data temporarily.

Important Things to Note

• When utilizing the “Unload Me” statement or hiding the UserForm using “Me.Hide”, it’s crucial to understand that the UserForm will not close automatically. To achieve the automatic closing of the UserForm, you need to explicitly trigger the code that includes the “Unload Me” statement or “Me.Hide.”
• When it comes to the functionality of closing a VBA Close UserForm after a specific time, it’s important to note that there is no built-in feature for automatically closing a VBA Close UserForm after a set duration. You would need to incorporate additional code logic, such as using timers or application events.
• You can store the desired value in global variables or properties within the VBA Close UserForm module before closing it. It allows you to access and use the stored value in other parts of your VBA code, achieving the desired outcome of VBA close UserForm and return value.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the VBA Close UserForm, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to VBA Close UserForm. Here we learn how to close UserForm in Excel VBA, difference between unload & hide, with step-by-step examples. You can learn more from the following articles –

• VBA ComboBox
• VBA List Box
• VBA ByRef Argument