UserForm Events

Add to Favorites
Author: | Edits: don

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.

e8b07d0918ca6ae091caf16e6f575838.jpg

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.

2249ca3516cdba06f33967fac40a9cc6.png

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

27c0dc67339f9604e0e29ca7d014d2ad.png

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:

92a2ee3595fd4c2c7e541e43a3fbc8c7.jpg

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.

73f8ede474909ae44ed256d3ca45ce65.png

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:

a4f1c2ff4929ec00d15c62f08c1f8eb1.png

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.  

Event Description

Activate

Excel activates the UserForm.

AddControl

Excel adds a run-time control to the UserForm.

BeforeDragOver

The user performs a drag-and-drop operation.

BeforeDropOrPaste

The user releases the mouse button to paste the data from the drag-and-drop operation.

Click

The user clicks the mouse on a UserForm object.

DblClick

The user double-clicks the mouse on a UserForm object.

Deactivate

The user deactivates the UserForm.

Error

Excel detects a UserForm control error.

KeyDown

The user presses a key.

KeyPress

The user presses an ANSI key. An ANSI key produces a visible character.

KeyUp

The user releases a key.

MouseDown

The user presses a mouse button.

MouseMove

The user moves a mouse on the UserForm.

MouseUp

The user releases the mouse button.

QueryClose

Excel closes the UserForm.

RemoveControl

Excel removes a control from the UserForm at run-time.

Scroll

The user repositions a Scroll box on a control.

Terminate

Excel terminates the UserForm.

Zoom

The user zooms the 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.

Question? Ask it in our Excel Forum


Downloadable Files: Excel File

Similar Content on TeachExcel
UserForm Controls Events
Tutorial: Explanation of Events for Controls in UserForms in Excel. This includes what they are, how...
Resizable UserForm
Tutorial: UserForm in Excel that can be resized by the user - they can click and drag the edges or c...
Transparent UserForm
Tutorial: Make a UserForm transparent in Excel; this allows you to see what is in the worksheet behi...
Remove the Title Bar from a UserForm
Tutorial: Remove the Title Bar and border from a form, including the red X, or close button. This cr...
Disable/Enable Buttons in UserForms
Tutorial: How to have a button in a UserForm disabled until the desired event occurs. For instance, ...
Make a UserForm in Excel
Tutorial: Let's create a working UserForm in Excel. This is a step-by-step tutorial that shows you e...