Userform Properties

The Userform can be seen as another object in VBA. And as any other object, a UserForm object has properties, methods, and events. In this section we look specifically at Userform properties, how to get and set properties of a Userform, and the VBA properties window for a Userform in Excel.

Userform name and caption

Some important properties are the Name and the Caption of the Userform. The Name is used to refer to that Userform from any other procedure and module in the VBA project. By default, Name and Caption are UserForm1, UserForm2, etc. We can change that in the properties window or with VBA code (see later). The picture below shows a Userform with the name “RegForm” and the caption “Registration Form”.

Now we can use that name to target the Userform with VBA code from any other procedure or module. For example, the picture above also shows the code window of a standard module (Module1) with a Sub procedure called “OpenUserform”, which opens the registration form when triggered. We could use any other methods or change any Userform properties from within that same procedure.

Note that we do not need to use the name and/or reference the Userform if we write code within the Userform module itself. In such case, we can get or set a property using the property alone (or method as we will see later) directly in the code window.

We can also use the VBA keyword “Me” to refer to the corresponding object for which the code is written, in this case, the Userform object. The advantage of using “Me” is that it allows to see all the properties and methods of the object when adding the dot afterwards (as with any other qualified Excel object).

Get or set properties

As we have seen just above, we can also get or set Userform properties from other procedure/module referencing the UserForm object by its name. For example, in the procedure “OpenUserform” we could specify the caption of the form or any other property before we show the form.

 With RegForm
     .Caption = "Other caption"
     .Show
 End With

 

This would override any given value for Caption in the properties window. As we will see later, the properties window is used to set properties (mostly appearance properties) at design time, while we can modify those properties later at run time with VBA code.

We can get or set other formatting properties such as background color, border color and style, font name and color, size, zoom, or scrolling. We can also add a picture inside the Userform or set a mouse image or status when hovering over the form. We can set the exact position where we want the Userform to appear (left, top) and size (height, width). Size at design time can be changed in the properties window or directly dragging the form.  

As explained earlier, any of these properties can be set at design time in the properties window or programmatically using the corresponding VBA property of the UserForm object. We have seen an example where the caption of a Userform was given in the calling procedure in a standard module. This is the most common case. As we will see later in this training, properties can also be set or initialized programmatically from within the Userform module itself using an the event procedure “UserForm_Initialize”. That procedure runs just before the Userform is shown and also overrides any values given in the properties window. As that procedure is inside the Userform module (as we have already seen above), the property can be used either alone or with the VBA keyword “Me”.

Despite most Userform properties are often set at design time, there might be situation where we want to change certain properties depending on the function of the form (the same Userform may be called from different procedures and for difference purposes).Thus, certain Userform properties such as Caption, Enabled, or Modal, could be modified at run time. That could also apply to some of the formatting/appearance properties, or even updating the background picture in the form depending on source data – for example, adding the flag of the country of origin of a user when showing data for that particular user.

The given example above makes more sense using a Picture control rather than changing the whole background picture of the Userform. As we will see later in this training, properties of controls such as a label, predefined values in a text box, the list in a combo box, or a particular picture in a Picture control, are far better examples of setting properties programmatically than any of the examples given of Userform properties.

 

Properties window

The properties window is especially helpful when working with Userforms. As explained earlier, it sets Userform properties at design time. If not visible, we can add the properties window from View -> Properties Window in the navigation tab or by pressing F4. The properties are listed alphabetically or by category depending on the tab selected. The image below shows the list of properties in alphabetical order.

We can see the Userform properties by category by choosing that tab (Categorized) in the properties window. The properties are then grouped in the following categories:

• Appearance
• Behavior
• Font
• Miscellaneous
• Picture
• Position
• Scrolling

The properties window also shows properties for each of the controls added to the form. The list of properties is slightly different for each of the controls. We will look at those when discussing form controls in another section. In the next section we will review the most important methods of the UserForm object.

<<< Previous | Next >>>