VBA MODULE 14

UserForm

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	Function
Label	Shows text
TextBox	Allows the user to enter text.
ComboBox	Displays a drop-down list.
ListBox	Displays a list of items.
CheckBox	Useful for on/off or yes/no options
OptionButton	Allows the user to select one of several options.
ToggleButton	A button that is either on or off
Frame	A container for other controls.
CommandButton	A button which can be clicked
TabStrip	Displays Tab
MultiPage	A tabbed container for other objects
ScrollBar	A draggable bar
SpinButton	A clickable button used for changing a value
Image	Holds an image
RefEdit	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”.

 

CONTROL	CAPTION	NAME
Userform	Employee Information	Employee_Form
Labels	As per your need	Not Required
Text Box	Employee Name	NameTextBox
List Box	Designation	DesignationListBox
Combo Box	City	CityComboBox
Option Button	YES	PassportButtonYes
Option Button	NO	PassportButtonNo
Command Button	Submit	SubmitButton
Command Button	Cancel	CancelButton

 

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 CommandButton1_Click() Employee_Form.Show 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 DesignationListBox.Clear   'Fill DesignationListBox With DesignationListBox     .AddItem "General Manager"     .AddItem "Manager"     .AddItem "Team Leader"     .AddItem "Associate" End With After 'Empty CityComboBox CityComboBox.Clear   '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"    Else       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.

Result: