How to use VBA Controls in UserForms

This article will demonstrate how to use Controls in UserForms in Excel VBA.

VBA has the ability to create forms that the user can interact with such as Invoice forms, or Contact details.  A form can contain a variety of Form Controls such as text boxes, Combo Boxes, List Boxes and Command Buttons.  This article will cover the most popular controls used on User forms namely Text Boxes, Labels, Combo Boxes, List Boxes, Check Boxes and Option Groups.

To find out more about built in user forms and creating custom user forms in Excel, click here.

Creating a UserForm

To create a UserForm in Excel VBA, we first need to open the VBE Editor.

To insert a new user form into your code, select the UserForm option from the Insert Menu.

A new user form will appear in the Project Explorer and will be shown in the Code Window on the right.

 

You can rename your form using the Properties box.  This should appear below your Project Explorer.

If it is how visible, Press F4 or click View, Properties Window.

 

Click in the Name property and rename your form.

We can now start populating our form with controls – the text box control is the most popular control along with the label control and command button.

To populate the form with controls, we need to switch on the Toolbox.

In the Menu, select View > Toolbox.

Creating a Text Box

To create a Text Box on your form, select the Text Box control in the Toolbox.

Drag a box shape on the form with your mouse, holding the left button down, and then release the mouse button.

 

 

As this is the first Text Box we are creating, it will be named TextBox1. As with the form, you can use the Properties window to rename the text box.

We can continue to create as many text boxes in our form that we require and name them as appropriate using the Name property in the Properties Window.

 

Creating a Label Control

A label control tells us what the text box is going to be used to hold. You drag on the form in the same way as you do for a Text Box, and then you can type the required text within the label control eg: First Name as shown below.

As with the text boxes, continue to create as many as are required in the form.

Creating a Combo Box Control

A Combo Box (or a drop-down box) allows the user to select from a list of values.   To create a combo box, click in the toolbox on the Combo Box control, and click and drag the combo box in your form.

 

Name the Combo Box appropriately.

Adding values to the Combo Box

In order to populate it with values, we need to code into the Initialize method of the User form object.

To get to the Code Behind the Form (CBF), either double click on the form, or click on the code button in the Project Explorer to switch to code view.

 

In the left hand drop down list, select User form, and then in the right-hand drop down list, select Initialize.

Type the following code within the Sub and End Sub to populate the drop down box:

Me.cboState.AddItem "Alabama"
Me.cboState.AddItem "Alaska"
Me.cboState.AddItem "Arizona"
Me.cboState.AddItem "Arkansas"

We can of course carry on an populate the list with the rest of the states in the USA!

To run the user form to see the list in action, 1) switch back to form view and then, 2) in the Toolbar, click on the Run button.

Click on the drop down arrow to see the list.

We can also use a Range in Excel to populate the Combo Box.

Private Sub UserForm_Initialize()
 Dim rng As Range
 For Each rng In Range("A1:A50")
   Me.cboState.AddItem rng.Value
 Next rng
End Sub

This will pick up any values stored in the Range A1 to A50 and populate the drop down list accordingly.

Creating a List Box Control

A List Box control work in the same way as a Combo Box control, but allows us to see all the options on the form in a list format.

Select the List Box control in the toolbox and then drag to create a list box on your form.

Adding values to the List Box

In the Initialize event of the form, type the following code:

Private Sub UserForm_Initialize()
  Dim rng As Range
  For Each rng In Range("A1:A50")
    Me.lstState.AddItem rng.Value
  Next rng
End Sub

When we run the form, the list box will be shown as demonstrated in the image below:

Creating a Check Box Control

Check Boxes enable the user to check or uncheck an option.

Select the Check Box control, and then click in the form where you wish it to go.

Amend the caption and name of the Check Box in the Properties Window.

Creating an Option Group Control with Option Buttons

An Option Group allows the user to select from a number of available options. The first step in creating an option group is to add a Frame Control to the form, and then to add the Option Buttons within the frame that has been added. By doing this, we ensure that when we run the form, only one of the Option Buttons within the frame can be selected at a time.

Select the Frame control in the toolbox and then drag to create a frame on your form.

Select the Option Button control in the toolbox and then click WITHIN the frame created above to add option button into the frame. Repeat as required.

Click on the frame and 1) amend the name of the frame and the Caption. Then 2) click on each of the option buttons and amend the Caption.

Creating a Command Button to Exit the Form

At this stage, the only way of closing the form is to use the close button in the right-hand corner of the form control bar. A more efficient way to exit a form, and one in which we have more control over, is by creating an Exit button on the form.  This is done by means of the Command Button Control in the Toolbox.

Select the Command Button control, and then click and drag in your form to create the button.

Using the Caption Property, change the caption of the command button to OK, and the Accelerator to “O”

 

The purpose of the Accelerator is for the user to use the keyboard to activate the button, in this case Alt+O would activate the button.

Adding Code to the Command Button

For the command button to work, we need to add code behind it so that when the button is clicked, the code runs. This is called the Click event of the button.

To get to the click event, double-click on the button in the design view of the form.

1) The Click event will be automatically created as this is the event that is most commonly used for Command buttons.

2) If we were to click in the Procedure drop-down box on the right hand side, we would see a list of all the Event Methods that are available to the Command Button.  This list changes according to what type of control we have created as there are different events available for different types of controls.

 

Type the following code into the click event of the command button.

Private Sub cmdOK_Click()
 Range("E1") = Me.txtFirstname
 Range("E2") = Me.txtSurname
 Range("E3") = Me.txtCellPhone
 Range("E4") = Me.cboState
 If Me.ckContactInfo = True Then
   Range("E5") = "SMS allowed"
 Else
   Range("E5") = "SMS not allowed"
 End If
 If Me.opt1.Value = True Then Range("E6") = Me.opt1.Caption
 If Me.opt2.Value = True Then Range("E6") = Me.opt2.Caption
 If Me.opt3.Value = True Then Range("E6") = Me.opt3.Caption
 Unload Me
End Sub

We can then run the form, fill in the text boxes and select from the drop down list.  We then click OK to enter the information into Excel.