VBA Combo box Add Items – from Excel Range or Array

VBA – Userform Combobox

In userform, we can have a combobox to display list of items as dropdown. User will be able to choose one of the items from this list.

To use this control, We should know how to add items, remove or find selected or active items.

This page has the code snippets that helps to add combo items in different ways.

1.Combobox ADDITEMS: Add constant values

If there is a list of items in a worksheet or if you would like to add defined number of items to a combobox, use this code.

Sub Combobox_Additems()
    'Add 1st item to index 0 - First item in list
    ComboBox1.AddItem "A", 0
    
    'Add 2nd item to index 1 - 2nd item in list
    ComboBox1.AddItem "B", 1
    
    'Add 3rd item to index 0 - so C becomes first in list
    ComboBox1.AddItem "C", 0
End Sub

Syntax: ComboboxObject.AddItems <value>, [<index>]

In the parameter “value” is mandatory. “index” is optional. You can either mention the index otherwise the system will assign the index on its own in incrementing order.

2.ComboBox Additems from Excel Range

It is easy to use the above methods to add items in combobox if there are defined set of entries.

But if there is a big list of items, then it is better to code a automated way through a loop like the below cde snippet.

Sub Combobox_Additems_from_Range()
    Dim iRow As Double
    
    'Add items in loop
    For iRow = 1 To 10
        ComboBox1.AddItem ThisWorkbook.Sheets(1).Cells(iRow, 1)
    Next
End Sub

It is possible to have identical or duplicate items in a list. So, if the above code is executed twice then same items will be added multiple times.

Make sure to clear Combobox items, before processing the loop or code in a way to avoid adding duplicate entries.