UserForm Events

Explanation of UserForm Events in Excel. This includes what they are, how to use them, and the most common Event used for UserForms.

Sections:

What is a UserForm Event?

How to Use an Event

Most Common UserForm Event

List of All UserForm Events

Notes

What is a UserForm Event?

Events are actions that happen within Excel, in this case, within a UserForm in Excel.

These actions can be caused by a user or other macros/VBA within Excel and includes things such as when the UserForm starts up, when the user clicks on it, when a key is pressed, when a control is added to or removed from the form, when it is resized, and more.

Events allow you to write code that will execute when the specific action occurs. In this case, the action will occur within the scope of the form.

Note: events are not just for UserForms; they also work with worksheets, workbooks, and with specific controls within a form and each one of these elements has events that are specific to them.

The code for events in forms goes into the code window specific to that form, as explained in the next section.

How to Use an Event

Events are placed wtihin the code window that is specific to the UserForm.

Go to the VBA window (Alt+F11) > look to the Project window on the left side (Ctrl+R) > look in the Forms folder > right-click the desired form > click View Code.

Once you do this, you should see the code window for that UserForm. This is where all the code that will control the form goes.

Look to the two drop-down menus at the top of this window:

The drop-down on the left contains all of the elements of the form, including the form itself, that can have an event attached to it.

The menu on the right contains all of the events that are specific to the element that you choose from the left drop-down menu.

Choose the UserForm element from the left menu:

Once you do this, a section of code will appear for whatever event was already selected in the right drop-down menu; don't worry about this if it is the wrong event, simply delete the section of code that appeared when that happened and then go to the right drop-down menu and select the correct event.

The most common event that is used with a UserForm is the Initialize event. The code for this event is run when the UserForm starts-up, or is initialized. (This event runs before the Activate event and before the form even appears on screen.)

Once you select an event, a code section will appear like this:

All code that you want to run when this event occurs should be placed within this section of code.

Simple Test

A simple way to test an event and to better understand when and how it runs is to put a message box pop-up window in the event so that you can visually see when the code will be executed.

In the above example, it would look like this:

Private Sub UserForm_Initialize()

MsgBox "hi"

End Sub

Run the form and you will see a pop-up window that says "hi" appear before you even see the UserForm. This shows you that the initialize event code runs before the user even sees the form.

This technique will help you to quickly learn what causes an even to trigger.

Most Common UserForm Event

The most common event for a UserForm is the Initialize event.

This is because this event triggers when a form is first run, or initialized. The event triggers before the form is even displayed and it is used to do many tasks that setup the form, such as to fill a listbox or combobox.

In most situations regarding forms in Excel, this is the only event that you will use for the form itself. However, you will use other events for the controls within the form, such as for buttons that a user can click.

List of All UserForm Events

These are all of the possible events that can be used with a UserForm.  

Notes

Events are very helpful in Excel and especially in UserForms. That said, you will almost always only use the Initialize event for a form. Most of the other events will simply never be needed. However, Control Events for forms will often be used; the next tutorial covers events for controls, which includes everything that you put onto the actual form, such as buttons, text inputs, and drop-down menus.

Events may seem confusing at first, but play around with them and follow the above examples and you will quickly start to understand how they work.

Make sure to download the sample file for this tutorial to see some good examples - the Initialize event will already be filled with some code, as well as some events used to make the control buttons work.