Khan Muhammad Kawser
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:
- Make a plan of how you will use your dialog box.
- Activate the VB Editor and insert a new UserForm.
- Then add the appropriate controls to the UserForm.
- You have to now create a VBA macro to display the UserForm. This macro will be created in a normal VBA module.
- 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.
Figure: An empty UserForm
If the Toolbox is not displayed automatically, activate the UserForm then choose View ➪ Toolbox.
Read More: How to Use Select Case Statement in Excel VBA (2 Examples)
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.
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. |
Similar Readings
- Excel Form Control vs. ActiveX Control (7 Differences)
- How to Import Data from a Website to Excel?
- 6 Best Excel VBA Programming Books (For Beginners & Advanced Users)
- Auto Sort When Data Is Entered in Excel (3 Methods)
- How to Make VBA Code Run Faster (15 Suitable Ways)
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.
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.
Read More: 22 Macro Examples in Excel VBA
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.
Read More: Excel VBA Worksheet Related Events and Their Uses
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
- Introduction to VBA Macros
- MsgBox & InputBox in VBA Excel : UserForm Alternatives
- Learn Excel VBA Programming & Macros (Free Tutorial – Step by Step)
- How to create VBA Macros in Excel using Macro Recorder
- Excel VBA Workbook Level Events and Their Uses
- 5 Differences Between Subroutine & Function in Excel VBA
