UserForm is a dialog box like msgbox() and inputbox() but with enhanced features and controls which helps user to input their desired values more efficiently.


Inserting a UserForm

Insert a UserForm object with the following steps:

  • Go to Visual Basic Editor (VBE) by pressing Alt+F11 from your excel sheet.
  • Click on Insert at the top menu of editor and select Userform from the dropdown list.


When you click on User Form, you will see a UserForm along with its toolbox at the left of it but you can move the toolbox anywhere on the screen using mouse as per your convenience


If you can't see the toolbox, or accidentally lose it, click the View menu at the top of the Editor. From the View menu, select Toolbox.


Adding controls to a UserForm

When you insert a UserForm, the VBE displays the Toolbox, as shown in the above figure. You use the tools in the Toolbox to add controls to your UserForm.

To add a control, just click the desired control in the Toolbox and drag it into the dialog box to create the control. After you add a control, you can move and resize it by using mouse.

Table below lists the various tools, as well as their functions. To determine which tool is which, hover your mouse pointer over the control and read the pop-up description.


Toolbox Controls 



Shows text


Allows the user to enter text.


Displays a drop-down list.


Displays a list of items.


Useful for on/off or yes/no options


Allows the user to select one of several options.


A button that is either on or off


A container for other controls.


A button which can be clicked


Displays Tab


A tabbed container for other objects


A draggable bar


A clickable button used for changing a value


Holds an image


Allows the user to select a range


Properties of a UserForm controls

Every control which you draw on a Userform has list of properties that you can change using Properties Window and it appears on the left of the Editor, just below the Project Explorer. If you can't see it, click the View menu at the top of the Editor and select Properties Window or simply press F4. You can drag the Property Window anywhere on the screen using mouse.

Each control has its own set of properties and many controls share same common properties.

In the below figure we have added two controls Label and TextBox from toolbox but Label control is selected so its property window is displayed to its right highlighting the caption property which is set to Employee Name.



Assigining VBA code to a UserForm

So far we have seen how to design a form and change its properties. Now we are going to design a new form which is shown in the below picture and associate a VBA code to it to make it working.



Execute the following steps.

1.Open the Visual Basic Editor.

2.Click Insert, Userform

3.Add the controls listed in the above picture using Tool Box.

a.Create a Level “Employee Name” and its text box then

b.Create Level “Designation” and its “List Box” then

c.Create Level “City” and its Combo Box then

d.Create a Level “Passport” and its option buttons and

e.Create two command buttons “Submit” and “Cancel”.

Once this has been completed, the result should be consistent with the picture of the Userform shown earlier.


4.Now we have to change the names and captions of the controls according to the table below. Names are used to prepare VBA code. To change the names and captions of the controls, click View, Properties Window, click on each control and change the properties. We have already changed captions while adding each control but we didn’t change the caption for our Userform itself, that’s why in the above figure you can see the form name is “UserForm1” so change it to “Employee Information”.






Employee Information



 As per your need

 Not Required

Text Box

Employee Name


List Box



Combo Box



Option Button



Option Button



Command Button



Command Button




5. After completing all the steps above we are moving to our final step of coding the form. So we will start with the code to show the UserForm.


To show the Userform, place a command button on your worksheet and add the following code line on that button:


Private Sub



End Sub



We are now going to create the Sub UserForm_Initialize. When you use the Show method for the Userform, this sub will automatically be executed.

a.In the Project Explorer, right click on Employee_Form and then click View Code.

b.Choose Userform from the left drop-down list. Choose Initialize from the right drop-down list.



c.Add the following code lines which will initialize the values in the form and make text boxes empty, list boxes and combo boxes filled and set option button to Yes option.


Private Sub UserForm_Initialize()

'Empty NameTextBox

NameTextBox.Value = ""


'Empty DesignationListBox



'Fill DesignationListBox

With DesignationListBox

    .AddItem "General Manager"

    .AddItem "Manager"

    .AddItem "Team Leader"

    .AddItem "Associate"

End With


'Empty CityComboBox



'Fill CityComboBox

With CityComboBox

    .AddItem "Delhi"

    .AddItem "Mumbai"

    .AddItem "Kolkatta"

    .AddItem "Chennai"

End With


'Set Option button to Yes

PassportButtonYes.Value = True


End Sub



Finally add bellows line just after the above code to set code for “SUBMIT” and “CLEAR” command buttons


Private Sub SubmitButton_Click()

   Dim emptyRow As Long

   'Determine emptyRow

   emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1


   'Transfer information

   Cells(emptyRow, 1).Value = NameTextBox.Value

   Cells(emptyRow, 2).Value = DesignationListBox.Value

   Cells(emptyRow, 3).Value = CityComboBox.Value

   If PassportButtonYes.Value = True Then

      Cells(emptyRow, 4).Value = "Yes"


      Cells(emptyRow, 4).Value = "No"

   End If

End Sub


Private Sub  ClearButton_Click()

  Call UserForm_Initialize

End Sub



 6. Its time to test the Userform


Go to the Excel sheet and click CommandButton1 to make the Userform appear.

Fill all the details on the form, click submit and following result (highlighted in red box) will appear on your screen.




© Snagoff. All right reserved.