Userform Module

The Userform module contains the layout and code for a UserForm object in Excel VBA. Modules contain the VBA code of Excel macros; you can read more about modules and procedures in this other page. But in this case, the Userform module contains the Userform layout along the VBA code (you will not see the code at first, just the layout). Let’s see how to insert a Userform module in Excel.

 

Insert a Userform

Userforms can be added to the VBA project just like any other module by either going to “Insert” in the menu bar or clicking the second icon in the general tool bar if visible (usually underneath the menu bar). Once added, the Userform shows up in the VBA project explorer under the folder “Forms” and a Userform template appears in the code window of the VBA editor.

The Userform gets by default the name UserForm1, UserForm2, etc., as we add them. The caption is also UserForm1, UserForm2, etc. by default. The name and caption can be changed in the properties window.

The Userform module can also be imported/exported as any other module. That’s useful to reuse the same forms in other projects. The file type associated with a Userform is .frm.

The Userform template shows a pattern of dots that allows placing and sizing controls as needed. Controls are added by dragging or selecting them from the tool box into the desired position in the form (we will talk about controls in another chapter).

 

Userform code

Userforms are linked to a VBA code window similar to that of other modules. We can use the Userform right-click menu to see the code window (view code) or the Userform layout (view object) by choosing the respective option there. We can also double-click the form or any of the controls to show the code. When using that approach, the code may select or create the “Click” event procedure of the Userform or the control (if available).

The code window for a new (empty) Userform is also empty and does not show any code. We could insert a regular procedure there and write code as we would do in any other module. However, it is good practice to have macros added to a standard module and keep here only Userform-related code (Userform event procedures, as we will see later). Sometimes, but rather rarely, we may create a Userform without any code at all such as for example an informative form with just some text in a text box. But it’s better anyway to add at least one button, and then, we would be having some code associated with the button to close the form, for example. Furthermore, it is probably not worth creating a Userform to just display a message; we would rather use a message box instead for that. We have discussed the advantages of Userforms vs message boxes in the previous chapter. You can also read more about message boxes here.

The code window has two fields on the top, one for the object and another for the procedure. This is similar to what we see in other modules. Initially, only the UserForm object is available in the drop-down; later, other objects representing each of the controls added (text boxes, buttons, etc.) would also appear.

 

Userform procedures

When selecting the UserForm object in the first drop-down, all the associated event procedures become available in the other drop-down. We will talk more about those procedures later in another chapter. This is similar to what we see in a sheet module when selecting “Worksheet”; it’s just a different set of event procedures (some may be similar though). For now, let’s only select the “Initialize” event procedure, but note that the “Click” even procedure is usually added right away when selecting the UserForm object (or when choosing to double-click the Userform to open the code window).

The “Click” event would trigger when clicking anywhere in the Userform. That’s usually not the goal of having a Userform in Excel though. We would rather have controls to get user input such as buttons that would be clicked to perform some actions (e.g. Save, Cancel, Close).

On the other hand, the event “Initialize” is often used to run some code just before the Userform is displayed (also known as initialize the form). There we can set up certain properties of the Userform or add some predefined values or settings for some of the controls. We can also populate combo or list boxes, for example.

 

We will learn more about “Initialize” and other events of the Userform in the following chapters. But before that, let’s have a look at the many properties of the Userform in Excel and the properties window of the Userform in the visual basic editor.

<<< Previous | Next >>>