onfexcel-how-to-add-item-to-a-combobox

How to Add Item to ComboBox (populate ComboBox with items)

Users can select from predefined values assigned to the ComboBox. The UserForm creator has to be define this Values.

For example: gender (male/female), age (1, 2, 3, 4,…), etc.

Populate ComboBox using a list from Excel

Sometimes we need to add values to a ComboBox as they are listed in our table or in our spread sheet.

If our list is standard we can simply use the .AddItem function and just type all the values. But this method is quite unfriendly especially when we have many values. Therefore we need to use a method that will make our code easier. We advise in using a LOOP. A loop will take the value of each cell from a predefined range.

We can include the loop in any of the above examples. Below we will give an example with a click event for a CommandButton.

Private Sub CommandButton1_Click()
    Dim i As Long, lr As Long ' define the variables
        Sheets("DataREG").Activate ' activate the sheet where you have the list to populate the combobox
        lr = Sheets("DataREG").Range("A" & Rows.Count).End(xlUp).Row ' find last row based on the last cell with values form column A. you can use any other column
        ComboBox2.Value = "" ' set values of combobox to nothing. this step will help avoiding double values
    For i = 2 To lr 'loop from row 2 to last row find. you can replace last row with a fixed row. for example in this case 7.
        With ComboBox2
            .AddItem (Cells(i, 1).Value) ' populate the combobox with the values from cell i,1
        End With
    Next
End Sub
0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *