Create a dropdown list (ComboBox) in a UserForm in Excel VBA

Steps to create a dropdown list in a UserForm in Excel VBA

1. Create the UserForm:
   • Open the VBA editor (press Alt + F11 in Excel).
   • Click on Insert > UserForm to create a new form.
2. Add a ComboBox:
   • From the toolbox that appears, choose the ComboBox control (dropdown list) and click on the form to add it.
3. Add code to populate the dropdown list:
   • You can populate the ComboBox in various ways: either manually entering the values, or pulling them from a range of cells in Excel.

Example Detailed Code

Let’s assume we want to create a UserForm with a ComboBox that contains options coming from a range of cells in an Excel worksheet. Here’s a complete example of code:

Step 1: Create the UserForm

In the VBA editor, create a new UserForm and add a ComboBox and a CommandButton to close the form.

Step 2: Code to populate the dropdown list

1. Code for the UserForm:

Open the code window of the UserForm and add the following code:

Private Sub UserForm_Initialize()
    ' Fill the ComboBox with data from an Excel range
    Dim rng As Range
    Dim cell As Range   
    ' Define the range of data (e.g., A1:A10)
    Set rng = ThisWorkbook.Sheets("Sheet1").Range("A1:A10")   
    ' Clear previous items in the ComboBox
    ComboBox1.Clear   
    ' Loop through each cell in the range and add its value to the ComboBox
    For Each cell In rng
        If cell.Value <> "" Then
            ComboBox1.AddItem cell.Value
        End If
    Next cell
End Sub

Explanation of the Code:

• UserForm_Initialize: This procedure automatically runs when the UserForm is opened.
• Define the range rng: We define the range of cells from which the values for the dropdown will be taken. In this case, the range is A1:A10 from the « Sheet1 ».
• ComboBox1.Clear: Before adding new items, we clear any old items in the ComboBox to avoid duplicates.
• For Each loop: This loop goes through each cell in the defined range, and if the cell is not empty, the value of the cell is added to the dropdown list using ComboBox1.AddItem cell.Value.

Add a button to close the UserForm:

Add a button on the UserForm with the following code to close the form when the user clicks it:

Private Sub CommandButton1_Click()
    ' Close the UserForm
    Unload Me
End Sub

Step 3: Code to open the UserForm

Now, you need to add a small piece of code to open the UserForm. You can place this code in a standard module.

Open the UserForm:

Go to a standard module (or create one) and add this code:

Sub OpenUserForm()
    UserForm1.Show
End Sub

This opens the UserForm with the ComboBox populated as soon as you run the OpenUserForm macro.

Step 4: Running the code

• Close the VBA editor.
• In Excel, run the macro OpenUserForm (press Alt + F8, select OpenUserForm, and click Run).
• You’ll see that the UserForm opens with the dropdown list filled with the values from the range A1:A10 in the « Sheet1 ».

Additional Option: Adding values directly in the code

If you want to manually add values to the ComboBox instead of pulling them from a range of cells, you can use this code in UserForm_Initialize:

Private Sub UserForm_Initialize()
    ' Add items manually to the ComboBox
    ComboBox1.AddItem "Option 1"
    ComboBox1.AddItem "Option 2"
    ComboBox1.AddItem "Option 3"
    ComboBox1.AddItem "Option 4"
End Sub

Summary

• UserForm_Initialize: This is where you add items to the ComboBox.
• ComboBox1.Clear: Clears existing items before adding new ones.
• ComboBox1.AddItem: Adds an item to the dropdown list.
• CommandButton1_Click: Code to close the UserForm when the user clicks the button.