How to Create a UserForm: an Overview

This article is part of my series: VBA & Macros in Excel – A Step by Step Complete Guide and Make UserForms in VBA Excel with Total 6 Tutorials.

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 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 user clicks the OK button, this is an event).

I have discussed how to create a UserForm in details 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 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.

Read More: How to Use the Select Case Structure in Excel VBA

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 in 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.

ControlDescription
Select ObjectsThis is the arrow sign in the Toolbox. It is not a control, it just helps you to select other controls by dragging.
LabelSelecting this will add a label in the UserForm. It just shows some text to the users.
TextBoxAdds a text box. The user can enter text that will be manipulated by the Subprocedure.
ComboBoxAdds a combo box. The combo box is basically a drop-down list.
ListBoxAdds a list box. This box allows the user to select an item from a list.
CheckBoxAdds a checkbox. If this control is checked, returns True, if not checked returns False.
OptionButtonAdds an option button. This allows the user to select one from multiple options.
ToggleButtonAdds a toggle button. When the toggle is on, returns True, when toggle off, returns False.
FrameAdds a frame. The frame contains other objects.
CommandButtonAdds a command button. When users click this button, some actions happen.
TabStripAdds a tab strip. It also contains other objects.
MultiPageAdds a multipage control. It also contains other objects.
ScrollBarAdds a scrollbar. This control lets users provide a value by dragging a bar.
SpinButtonAdds a spin button. This also lets the user provide a value by clicking up or down.
ImageAdds a control that can contain an image.
RefEditAdds 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.

Read More: How to create an Excel VBA UserForm

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. In 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 in the top of the Properties window.

In the above discussion, we have talked about changing the properties via the Properties window, it 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.

Read More: Creating a UserForm that will change cases to Upper, Lower or Proper

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:
How to create an Excel VBA Userform.
Creating a Userform that will change cases to Upper, Lower or Proper.

Dig them to learn more about creating UserForms.

Happy Excelling ☕

Read More…

MsgBox & InputBox in VBA Excel : UserForm Alternatives


Hello! Welcome to my Excel blog! I am conducting deep dives into the world of Excel. Please join with me and explore Excel deeply. Keep in mind this African proverb: "If you want to go fast, go alone, If you want to go far, go together." Let's together explore Excel deeply! ☕

2 Comments

      Leave a reply