UserForm Events in VBA

In this tutorial, we will discuss about Events related with Load and Unload of UserForm in VBA.

Before jumping to the UserForm events, let us understand what is an event in VBA.

What is event in VBA?

An event is an action that can trigger the execution of the specified macro.

For example, when you open a new workbook, it’s an event. When you insert a new worksheet, it’s an event. When you double-click on a cell, it’s an event.

There are many such events in VBA, and you can create codes for these events. This means that as soon as an event occurs, and if you have specified a code for that event, that code would instantly be executed. Excel automatically does this as soon as it identifies that an event has taken place. So we only need to write the code and place it in the correct event subroutine.

In Excel, there are different types of events. Example,

• Worksheet Level Events
• Workbook level events
• Application level events
• Chart Events
• OnTime and OnKey events
• User Form Level Events

In this post, we are going to focus on UserForm level events which are related with Showing and Closing User Form.

There are five events which gets triggered at the time of Showing and Closing the Form.

1. Initialize
2. Activate
3. Deactivate
4. QueryClose
5. Terminate

Let’s move to Excel window and understand all these events with example.

We need to open the Visual Basic Application Window. We can open Visual Basic window through Developer tab or pressing shortcut key ALT + F11.

To open Visual Basic Application window, just click on Developer Tab, then under Code Group, click on Visual Basic Application button.

Alternatively, you can also use Short cut key ALT + F11 to open the VBA Window.

After clicking on VBA button, you will see the Visual Basic Application window as shown in below image.

To understand the UserForm Events, we need to add Form controls to our project.

Let’s add two different blank UserForm in this project.

To do that, just click on Insert Menu and then select UserForm.

Here, we have inserted a blank UserForm.

Repeat the same process to add the second form. Let’s make both the UserForm modal less.

Select the UserForm1 and go to properties window. Change the ShowModal properties from True to False.

Do the same for UserForm2 as well.

Let’s add a Command Button on UserForm1, change the name as cmdShow and Caption as Show.

Add one more Command Button on UserForm1, change the name as cmdClose and Caption as Close.

Now, double click on cmdShow and write code to open the UserForm2 on click event of Show button.

Private Sub cmdShow_Click()
    UserForm2.Show
End Sub

Let’s move to UserForm1 and double click on cmdClose button. Write the code the close the UserForm1.

Private Sub cmdClose_Click()
  Unload Me
End Sub

Note: Here, UserForm2 is required to understand the events related with UserForm especially Deactivate event.

Now, we have done with all the basic requirements to understand all the events with example.

Let’s start with Initialize form event.

Initialize event run the Sub Procedure code every time the UserForm is loaded into Excel memory but before showing it on screen.

The Initialize event is typically used to prepare an application or UserForm for use. As for Example, to assign the default values to Variables, initialize the form controls with some predefined values and change the back colors etc.

Let’s double click on UserForm1 and add the code to Initialize event.

Here, you can see that we are in Click event. Let’s add the Form Initialize event from the second drop-down. First drop-down is to select the object where we are going to add the code. Here, UserForm has been selected.

Let’s move to second drop-down and select the Initialize event.

Here, you can see that a blank Sub Procedure for Form Initialization has been added in our code window.

Let’s add code here to show message on Form Initialization and change the back color of Show Button.

Private Sub UserForm_Initialize()
    MsgBox "I am getting intialized. Let me change the back color of Commandbutton1."
    cmdShow.BackColor = vbRed
End Sub

Now, coding for UserForm Initialize  event has been done. Whenever this form will be loaded to the memory while running the project then it will pop-up a message and change the back color of cmdShow to Red.

Let us run this form and see the demo.

Now, close this form and move to Coding window to understand the UserForm Activate event.

The Activate event occurs when the form becomes the active window. This event will trigger the code after initializing the Form or activating the form. We can use Show method to trigged the Activate event.

Let’s choose the Activate event from the drop-down.

Now write the code to pop-up a message box stating that “I am an active window now.”

Private Sub UserForm_Activate()
    MsgBox "I am an active window now."
End Sub

Let’s run this form again. Here, you can see it’s initializing the form and once we click on Okay button for Initialize message box then it triggers the Activate events and run the code assigned on that sub procedure.

Let us close UserForm and move to code window to understand the Deactivate UserForm event.

The Deactivate event occurs when the UserForm is no longer the active window.

Suppose, we are moving from UserFrom1 to UserForm2 in our example then Deactivate event for UserForm1 will trigger and run the code assigned on that sub procedure.

Let’s select the Deactivate event from the drop-down to add the Sub Procedure.

Now, add the code to show a message box stating “I am no longer an active window.”

Private Sub UserForm_Deactivate()
    MsgBox "I am no longer an active window."
End Sub

Let us run this UserForm1 again and see what is happening once we move away from the UserForm1.

Let’s click on Show Button to show the UserForm2. Once, we will click on Command Button then UserForm1 will be deactivated and it will run the code available in UserForm_Deactivate sub procedure.

Let me move the UserForm2 little bit right side as it is showing on the top of UserForm1.

Now, click on UserForm1. As this window is active hence it triggers the Activate event.

Let’s close both the form and move to Code window of UserForm1.

The fourth event is related with unload the UserForm i.e. QueryClose.

QueryClose event runs the code before Unloading the UserForm from the memory.

Let us add the QueryClose event in our code and then understand this event and all the required parameters.

Here, we can see that this procedure has two different Parameters.

1. Cancel
2. CloseMode

We can pass True or False to Cancel parameter. True is to cancel the close activity and False for closing the form.

With the help of CloseMode parameter, we can disable the mode of closing the UserForm.

Any UserForm can be close by clicking on Close Button on form, writing code to Unload the Form, Closing the parent application and the last one is through Task Manager. Values are from 0 to 3 respectively for all these closing mode. Let’s write the code to validate whether user has clicked on Close Button available on top of the form and disable it.

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = 0 Then
        MsgBox "You can't use close button here."
  Cancel = True
    End If
End Sub

Let’s run this form and see how it works.

Let’s click on X button to close this form. As we have already disabled this button hence it’s not working.

To close this form, we can use the Close command button available on this form.

Now, we have only one event pending i.e. Terminate

The Terminate event occurs after the UserForm is unloaded from the memory or in a simple language we say that it will run the code after closing the UserFrom.

Please note that the Terminate event isn’t triggered if the instances of the UserForm is removed from memory because the application terminated abnormally.

Let’s add the Terminate event and write down the code.

Private Sub UserForm_Terminate()
    MsgBox "Good bye!"
End Sub

Let’s run the user form and then close the form and see what is happening. Here, you can see that after closing the form it’s running the code assigned on Terminate event.

This is all about the VBA Events related with Loading and Unloading the UserForm. If you are interested to learn more about VBA events for UserForm, Worksheet, Charts, Application then please leave your comment in comment section. I will post more tutorial on that.

Please watch the step-by-step tutorial on YouTube.

Click on below button to download the Excel file.