A User Form is a built-in customized dialog box that fetches data from the user through a user-friendly dialog box or window that makes up part of an application’s user interface. It enables a user data entry operation easier, controllable, and manageable to use for the user. With the help of User forms, the users can easily enter data in a workbook that they can create in an Excel VBA.  It functions as a VBA container and can add various controls as per the required functionality, each of which has certain usage and related properties.

Basics of Userform

Userform Controls

Controls are a part of VBA userform that makes the form easier to implement and for each control the user can easily set a property to a certain value. You can modify the size or position then or can do it later as well by entering design mode. When you add a control, to change its properties, you can go into ‘Design mode’ by clicking on the button next to ‘Insert’ in ‘Controls’.

Steps to add a control in excel:-

• Go to the Developer tab.

Basics of Userform


• In the Controls group, click on insert.

Basics of Userform
Basics of Userform


• Select the control you want to use and create that on your worksheet using your mouse

Basics of Userform


The Different controls available in form control are as follows:

  1. Button
  2. TextBox
  3. Label
  4.  Check Box
  5. Option Button
  6. List Box
  7. Combo Box
  8. Spin Button
  9. Scroll Bar
  10. Toggle Button
  11. Frame Control
  12. Multipage Control

Button

Basics of Userform


By pressing the Button, you can execute a macro. The user can attach the event of the Command button to a VBA code and can run any logical statement and action. A single click on the commandbutton is used to rename the button, and a double click enables you to access the event. In the below given example, we have created a close button in the userform and have added action to it (whenever the user will click on the close event and message box will pop up showing certain message).

Example 1: Write a macro in VBA, to demonstrate the example of a button.

Output

Basics of Userform
Basics of Userform


TextBox

Basics of Userform

This control is used to accept data from the user. You can customize the textbox as per your requirement. You can specify the number of characters to be fed with the help of MaxLength Property. You can even specify what characters to be displayed irrespective of what has been enter by enabling the PasswordChar Property. You will set all the properties in the property window and can enable or disable the specifications as per your requirement.

Example 1

Output

Userform:

Basics of Userform

After running the program:

Enter your first name and last name.

Basics of Userform

Click on submit. The following msgbox will get displayed.

Basics of Userform

Label

Basics of Userform


This can also be used as a button but is more often used put in front of another Control to explain what that control stands for, but it is mostly used to Textbox. The bel can be formatter either in the format window or with the help of VBA code. In the below example, we have used ‘.caption’ to format the text for label and various other syntax to alter the  properties of ‘label1’.

Example 1:

Output

Userform:

Basics of Userform

After running the program:

Basics of Userform

Press the click button.

Basics of Userform



Check Box

Basics of Userform

You can have as many check boxes you want and they all will be independent of each other. If a checkbox is checked in, then it will return ‘TRUE’ else ‘FALSE’ to a linked cell. You can also have another category for a null value, to achieve this set the value Triple state property (can be done through VBA code or through properties window) to True.

Example 1:

Output

Userform:

Basics of Userform

After running the code:

Basics of Userform

Select either of the checkbox. Here for instance we have selected No.

Basics of Userform

Click on the submit button. The following message box will be displayed.

Basics of Userform

Option Button

This is slightly different to check boxes just that you can copy multiple option buttons together but all of them would be dependent on each other i.e. at one time, only one will be selected and the others will be turned off. It works like a radio button. And when you link a cell, the cell will give the number of the option which is currently selected.

Example 1

Output

Userform:

After running the code:

Basics of Userform

Selecting the yes radiobutton. It will show the following message alert:

Basics of Userform


List Box

Basics of Userform

The List box allows the selection of one or more items from a list. In this one or more than one value can be visible a time. To give the range, once you have created a list box, just do a right-click and select ‘Format Controls’ – in the ‘Input Range’, select the cells which you want to see in the list box and then select a cell where you want to output which item of the list is selected currently.

Example 1

Output

Userform:

Basics of Userform

After running the program:

Basics of Userform
Basics of Userform

Combo Box

The Combo Box is similar to the list box just that it has a drop-down – so you can only see the selection and no other value. In other words, it is a combination of ListBox and TextBox. In this, only one row of elements is displayed at a time. It will return the position for the given item.

Example 1

Output

Userform:

Basics of Userform

Alter running the code:

Basics of Userform

Spin Button

Basics of Userform

This button helps the user to increase or decrease the value of a related cell by a pre-defined volume. To use this, simply select the spin button and create one – do a right-click and go to ‘Format Controls’ – write the values as per your requirements and select the linked cell – using spin button then, you can increase or decrease the value of the linked cell.

Example 1

Output

Userform:

Basics of Userform

After running the code

Basics of Userform

Scroll Bar 

The Scroll Bar Form Control, often referred to as a Slider is a simple linear slider that allows the increase or decrease of a linked cell’s value by sliding a bar either left/right or up/down. Similarly, the values can be filled to the rest of the controls we have discussed so far.

Example 1:

Basics of Userform

Output

Basics of Userform

The amount changes with respect to the scroll bar.

Basics of Userform

Toggle Button

This control acts as an on-off state. It calls for action when pressed first and calls different action when pressed for the second click. When the button is selected, it returns a Boolean value True and returns Boolean False when it is unselected.

Example 1

Output

UserForm:

Basics of Userform

After running the VBA code:

  1. For the First click, column A is hidden
Basics of Userform
  • For the second click, Column B is hidden
Basics of Userform

Frame

It is used to group the controls that work collectively, or have any similarities, or are related to each other in a UserForm. Frames enhance the UserForm’s layout by organizing and clustering the same sort of items.

Multipage Control

Basics of Userform

A MultiPage control organizes data into different categories, wherein each category has its own individual Page. It is commonly used when the user handles the bulk of data. It can take one or more Page objects, where each page has its own set of controls. In this, all controls are separately contained or added in a Page to reduce the ambiguity otherwise, all the controls were a part of the UserForm. 

Example 1

Output

Userform:

Basics of Userform

After running the VBA code:

Basics of Userform

TabStrip

Basics of Userform

A TabStrip control is a collection of Tabs wherein each Tab contains a set of controls. It looks at different contents contained in each Tab, for each set of controls. By default, this control has 2 Tabs. The control’s content can change if a different page is selected, but the display of the layout stays the same. For adding new tabs, right-click on the empty space next to tab2 and select a new page.

Example 1:

Output

Userform:

Basics of Userform

After running the code

Basics of Userform

Pin It on Pinterest

Share This