[VBA] Guide to Using ComboBoxes: Creating Drop-down Lists and Getting Values

In VBA UserForms, the “ComboBox” control is very useful when you want users to choose one item from a predefined list, such as department names or product categories.

Since choices appear in a drop-down format when clicked, it also helps save space on the form.

This article explains the basic steps to set up a list of options in a ComboBox and retrieve the item selected by the user.

Sample Code for Setting Up and Getting Values

This macro consists of two parts:

1. UserForm_Initialize: Sets up the list of options and displays the initial value when the form opens.
2. OKButton_Click: Retrieves the value selected in the ComboBox and writes it to the active cell when the “OK” button is pressed.

' Event executed when the form is initialized
Private Sub UserForm_Initialize()
    With Me.DepartmentComboBox
        ' Set list items by passing an array to the .List property
        .List = Array("HR", "General Affairs", "Sales", "Development", "Accounting")
        
        ' Set the initial selected item using .ListIndex (0 is the first item)
        .ListIndex = 0
        
        ' Set to drop-down list style (non-editable) using .Style
        .Style = fmStyleDropDownList
    End With
End Sub

' Process when the "OK" button (OKButton) is clicked
Private Sub OKButton_Click()
    ' Get the value of the selected item using .Value and write it to the cell
    ActiveCell.Value = Me.DepartmentComboBox.Value
    
    ' Close the form
    Unload Me
End Sub

Preparation Before Execution

1. Create a UserForm.
2. Place a ComboBox and a CommandButton on the form.
3. Change the (Name) of the ComboBox to DepartmentComboBox and the CommandButton to OKButton.
4. Paste the code above into the form’s code module, then run the form.

Explanation of the Code and Properties

.List = Array(…)

This sets the list of items to display in the ComboBox. Assigning an array created with the Array function directly to the .List property is the easiest method.

.ListIndex = 0

This specifies which item should be selected by default using an index number (starting from 0).

• Setting it to 0 displays the first item in the list (in this example, “HR”).
• If you do not want anything selected initially, set this to -1.

.Style = fmStyleDropDownList

This sets the style of the ComboBox.

• fmStyleDropDownList: Creates a drop-down list. Users can only select from the list and cannot type their own text. This is best when you want to restrict choices to fixed options.
• fmStyleDropDownCombo: Allows users to select from the list OR type text directly (this is the default setting).

ActiveCell.Value = Me.DepartmentComboBox.Value

To get the item selected by the user, use the .Value property (or .Text property). In the OKButton_Click event, the code retrieves the selected department name and writes it to the active cell.

Summary

In this article, we covered the basics of using a ComboBox in a UserForm to create a drop-down list and retrieve values.

1. Use the .List property to set your options using an array.
2. Use the .ListIndex property to decide which item is shown first.
3. Use the .Style property to control whether users can type freely or must choose from the list.
4. Use the .Value property to get the selected data.

The ComboBox is an excellent control that allows you to present many options to the user within a limited space. Mastering this will help you create user-friendly forms.