How to Create a UserForm: an Overview

Get FREE Advanced Excel Exercises with Solutions!

In many cases, it is adequate to use InputBox and MsgBox functions. Using InputBox function, a user can provide only a single input to Excel. So, to work with many information from the users, you have to create a UserForm.

To create a UserForm, use the general steps below:

  1. Make a plan of how you will use your dialog box.
  2. Activate the VB Editor and insert a new UserForm.
  3. Then add the appropriate controls to the UserForm.
  4. You have to now create a VBA macro to display the UserForm. This macro will be created in a normal VBA module.
  5. Lastly, you will create the event handler VBA procedures. These procedures are executed when users perform some events(for example, sometimes the user clicks the OK button, this is an event).

I have discussed how to create a UserForm in detail in the following sections.

Working with UserForms

Our first step is to create a new UserForm in the VB Editor window. Choose Developer ➪ Code ➪ Visual Basic or press Alt+F11 to activate the VB Editor. Then select the correct workbook in the Project window and then choose Insert ➪ UserForm.

The VB Editor displays an empty UserForm, shown in the figure below. When you activate a UserForm, the VB editor also displays the Toolbox. From the Toolbox, we shall add controls to the UserForm.

How to Create a UserForm: an Overview

Figure: An empty UserForm

If the Toolbox is not displayed automatically, activate the UserForm then choose View ➪ Toolbox.


Adding controls to UserForms

The Toolbox contains a good number of necessary ActiveX controls that you can use to make your UserForm. Move your mouse pointer over the controls in the Toolbox, you will see the control’s name popped up.

To add a control to the UserForm, just click any of the controls of the Toolbox and then click on the UserForm, the control will be added to the UserForm. You can resize and change the location of the control that you have already added to your UserForm using your mouse.

How to Create a UserForm: an Overview

Adding controls to the UserForm1.

In the following table, I have shown all the controls of the Toolbox.

Control Description
Select Objects This is the arrow sign in the Toolbox. It is not a control, it just helps you to select other controls by dragging.
Label Selecting this will add a label to the UserForm. It just shows some text to the users.
TextBox Adds a text box. The user can enter the text that will be manipulated by the Subprocedure.
ComboBox Adds a combo box. The combo box is basically a drop-down list.
ListBox Adds a list box. This box allows the user to select an item from a list.
CheckBox Adds a checkbox. If this control is checked, returns True, if not checked returns False.
OptionButton Adds an option button. This allows the user to select one from multiple options.
ToggleButton Adds a toggle button. When the toggle is on, returns True, when toggle off, returns False.
Frame Adds a frame. The frame contains other objects.
CommandButton Adds a command button. When users click this button, some actions happen.
TabStrip Adds a tab strip. It also contains other objects.
MultiPage Adds a multipage control. It also contains other objects.
ScrollBar Adds a scrollbar. This control lets users provide a value by dragging a bar.
SpinButton Adds a spin button. This also lets the user provide a value by clicking up or down.
Image Adds a control that can contain an image.
RefEdit Adds a reference edit control. It allows the user to select a range.

Changing the Properties of a UserForm Control

Every control that you add to a UserForm from the Toolbox has several properties. These properties determine how the control looks and behaves. Height and Width properties can be changed by clicking and dragging the border of the control. You have to use the Properties window to change the other properties.

To display the Properties window of a control, click on the control to select it and then choose View ➪ Properties Window (or press F4). The Properties window displays a list of properties for the selected control. Each control has a different set of properties. If you choose View ➪ Properties Window after selecting the UserForm, the Properties window will display properties for the UserFrom.

I have added the CommandButton control in the UserForm and then chosen View ➪ Properties Window. As result, the following figure generates.

How to Create a UserForm: an Overview

The Properties window for a CommandButtoncontrol.

To change a property, you have to select the property in the Properties window and then enter your preferred value. You have to select the values from a list to change some properties like BackColor, BackStyle, ForeColor, and etc. You will find the list of controls in a drop-down list at the top of the Properties window.

In the above discussion, we have talked about changing the properties via the Properties window, which is called design-time property change. We can also use VBA to change the properties of controls while the UserForm is displayed, it is referred to as runtime change.

I have not covered all the properties in this chapter, but from time to time in different articles, I shall work with many of them. If you want to know about a particular property, just select it in the Properties window and press F1. Online help docs will appear to help you, so your internet connection must be OK to get the help.

Handling events

An event is something that happens when the user does something with a control. For example, the user may click a button: it is an event. Selecting an item in a list box control also treated as an event.

What will happen, when an event has occurred, depends on the Sub procedure written for the event. The Sub procedure is called the event handler.

To recognize easily an event handler procedure, the name of the Subprocedure is written using both the control name and the event name. The general form to write an event handler Sub procedure name is: at first use the control name, then an underscore, and then the event name. For example, the procedure that is executed when the user clicks a button named MyButton is MyButton_Click.

Displaying a UserForm

To display the UserForm, you have to write a procedure. You use the Show method of the UserForm object. To display the UserForm named UserForm1, I have used the following procedure:

Sub ShowDialogBox()
    UserForm1.Show
End Sub

This procedure must be stored in a regular VBA module, remember not in the code module for the UserForm. If your VB project doesn’t have a regular VBA module, create choosing Insert ➪ Module to add one.

When the ShowDialogBox procedure is executed, the UserForm is displayed. What happens next depends upon the event handler procedures that you create.

UserForm Examples

I have created two UserForms with proper explanation in the following links:

Dig them to learn more about creating UserForms.

Happy Excelling ☕


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Kawser Ahmed
Kawser Ahmed

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only a how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can check out my courses at Udemy: udemy.com/user/exceldemy/

2 Comments

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo