VBA ComboBox

Written by

Mel Jenkins

Reviewed by

Steve Rynearson

Last updated on March 23, 2022

This tutorial will demonstrate how to work with ComboBoxes in VBA.

ComboBoxes allow users to select an option from a drop-down menu list. ComboBoxes can be created in VBA UserForms or with an Excel worksheet. In this tutorial, you will learn how to create and manipulate ComboBoxes in VBA and in Excel worksheets.

If you want to learn how to create a Listbox, click here: VBA Listbox

If you want to learn how to create a Checkbox, click here: VBA Checkbox


Create a ComboBox in Excel Worksheet

In order to insert a ComboBox in the Worksheet, you need to go to the Developer tab, click Insert and under ActiveX Controls choose Combo Box:

vba insert combobox

Image 1. Insert a ComboBox in the Worksheet


When you select the ComboBox which you inserted, you can click on Properties under the Developer tab:

vba combobox properties

Image 2. Change ComboBox Properties


Here you can set different properties of the ComboBox. To start, we changed the attribute Name to cmbComboBox. Now, we can use the ComboBox with this name in VBA code.


Populate a ComboBox in VBA code

First, we need to populate the ComboBox with values. In most cases, a ComboBox needs to be populated when the Workbook is opened. Because of this, we need to put a code for populating the ComboBox in object Workbook, procedure Open. This procedure is executed every time a user opens the Workbook. Here is the code:

With Sheet1.cmbComboBox

    .AddItem "John"
    .AddItem "Michael"
    .AddItem "Jennifer"
    .AddItem "Lilly"
    .AddItem "Robert"

End With

When you click on the drop-down menu, you will get 5 names to choose from (John, Michael, Jennifer, Lilly and Robert):

vba combobox populate

Image 3. Populate the ComboBox in VBA


Populate a ComboBox from a Cells Range

Another possible way to populate a ComboBox is to let a user do it. A ComboBox can be linked to the cells range. In this approach, every time a user enters a new value in the cells range, the ComboBox will update with that value.

If you want to enable this, you have to go to the Properties of the ComboBox and set the attribute ListFillRange to the cells range (in our case E2:E5):

vba populate combobox from cells range

Image 4. Populate the ComboBox from the cells range


We linked our ComboBox with the range E2:E5, where we put names we want (Nathan, Harry, George, Roberta). As a result, the ComboBox is now populated with these names:

vba populated combobox

Image 5. Populated ComboBox from the cells range


Get a Selected Item of a ComboBox in VBA

The purpose of a ComboBox is to get a users choice. In order to retrieve a users choice, you need to use this code:

Dim strSelectedItem As Variant

strSelectedItem = Sheet1.cmbComboBox.Value

The users selection is in the attribute Value of Sheet1.cmbComboBox object. This value is assigned to the variable strSelectedItem:

vba combobox get selected value

Image 6. Get a selected value from the ComboBox in VBA


We selected Julia in the ComboBox and executed the procedure. As you can see in Image 5, the value of the strSelectedItem is Julia, which is the value we selected. Now you can process this variable further in the code.


Clear a ComboBox

If you want to clear a ComboBox in VBA, you need to use Clear method of Sheet1.lstComboBox object. It will delete all the items from the ComboBox. Here is the code:


Notice that the Clear method does not delete the attribute ListFillRange, so it must be removed from the properties of the ComboBox beforehand.

When we execute the code, we get the empty ComboBox:

vba clear combobox

Image 7. Clear the ComboBox


Use a ComboBox in a Userform

As we mentioned, Combobox is most often used in Userforms. To explain how you can do it, we will first insert an Userform. In VBA editor, right-click on Module name, click on Insert and choose UserForm:

vba combobox insert userform

Image 8. Insert a Userform


To display controls for inserting, you need to enable the Toolbox. To do this, click on the Toolbox icon in the toolbar. After that, you will get the windows with all the controls available. You can click on ComboBox to create it in the Userform.

vba combobox insert in userform

Image 9. Insert a ComboBox in the Userform


We will name the ComboBox cmbComboBox. In order to populate it with values, we need to put the following code into the method Initialize of the object UserForm:

Private Sub UserForm_Initialize()

    With UserForm1.cmbComboBox

        .AddItem "John"
        .AddItem "Michael"
        .AddItem "Jennifer"
        .AddItem "Lilly"
        .AddItem "Robert"

    End With

End Sub

This code triggers every time a user runs the Userform and populates the Combobox with these 5 names:

vba combobox in userform

Image 10. The ComboBox with values in the Userform


If you want to get selected value from the ComboBox, you need to use the same logic for the Combobox in a Worksheet, which is explained earlier in the article.

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users! vba save as

Learn More!

VBA Code Examples Add-in

Easily access all of the code examples found on our site.

Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.

(No installation required!)

Free Download

Return to VBA Code Examples