Introduction to Userforms

Excel Userforms are VBA-programmable forms that can be customized to create a powerful user interface. They are often used to get input from users or display information. Excel offers a broad choice of controls such as text boxes, drop-downs, buttons, and other controls to create interactive forms. Furthermore, the visual basic editor in Excel allows to easily design the Userform by simply dragging and moving controls. A Userform in Excel may look like the example below.

 

 

Userform vs message and input boxes

Userforms enhance by far the limited functionality of Excel message and input boxes. A message box displays a message and allows the user to acknowledge, confirm, or reject the message to some extent. The MsgBox function is used to prompt a message box in Excel. It displays a dialog box with information and buttons, and it stops program execution until one of the buttons is clicked. The following line of code displays a simple message box.

 MsgBox "Hello"

The MsgBox function can take additional arguments to customize the title and buttons. A message dialog box shows by default the “OK” button, but there are altogether six button alternatives. Read more about the message box in Excel in this other page.

Another function often used to get input is InputBox. This is probably the simplest way to get input from users in Excel. The InputBox function prompts an input box that can accept input as a number, a string, or a range. The following piece of code displays an input box asking for the name.

 Dim uName As String
 uName = InputBox ("WhatꞋs your name?")

We can also customize the title and default input text. However, the Excel input box is limited to one field to input a value and the two buttons (OK and Cancel). It is commonly used for basic interaction with the user.

On the other hand, Excel Userforms allow to fully design the look of the dialog, add as many buttons as needed, and display information and get input from users in many different ways. They offer a large variety of controls to accept user input such as text boxes, combo boxes, check boxes, etc. We will see all that in the following sections.

 

Userform components

The Userform is added as a module and consists of a template layout associated with VBA code. A Userofrm in Excel is represented by the UserForm object in VBA. You can read more about the Excel object model in this other page. Thus, as many other objects in the Excel object model, the UserForm object has associated properties (also methods and events). We can see all the properties of the Userform (or the UserForm object), such as name, size, color, and many other, in the properties window. 

We can add controls to the Userform such as labels, text boxes, buttons, etc., by simply dragging them in from the tools box. We can also see the properties for each control in the properties window. The Userform and its controls have also methods and associated events, as many other objects in Excel. You can check this other page if you do not know what properties, methods, and events are in Excel VBA. The following picture shows each of those components.

We are going to cover each of these components in the following chapters. The next chapter explains how to add a Userform module to the Excel VBA project and display the VBA code window associated with the Userform. After that, we will look into Userform properties. There is a long list of properties that can be easily set to build the desired Userform display and functionality. That can be done manually or with VBA code. Then we will look into each of the controls that can be added to a Userform in Excel and how to work with them. We will also learn how to use methods (actions) and events for the Userform and its controls. Finally, we will look at some practical examples. But there are many other interesting thigs that we can do with Userforms in Excel.

Showing a Userform

We can show the Userform while coding or testing by clicking the “Play” button that says “Run Sub/Userform” in the standard tool bar of the VB editor or pressing F5. On the other hand, when ready as part of a macro or VBA project within a workbook or add-in, the Userform is launched from a sub-procedure in a standard module (e.g. Module1) as indicated below.

 Sub ShowForm()
     UserForm1.Show
 End Sub

That macro can for example be triggered from a button, via an event, from another macro, or by any other way to call a macro (see 10 different ways to call a macro). "UserForm1" is the default name of the form but can be any other given name.

A Userform can be closed by clicking the red cross at the top-right corner of the form if that action has not been deactivated. But Userforms often have specific controls (e.g. buttons) to close the form among other actions (see later under Userform controls). The Userform can also be hidden, instead of fully closed. As we will see later, depending on the status of the ShowModal property, we can continue working on the worksheet while the Userform is open (when ShowModal is set to False) or cannot (when set to True).

 

Next >>>