How to Use VBA to Populate ComboBox List from Range in Excel

Certainly, VBA Macros help to automate repetitive tasks and save time and effort in Microsoft Excel. However, we can also insert drop-down lists using VBA code, enabling us to add interactive elements to our otherwise mundane worksheets. Granted this, in this article, we’ll learn how to populate a ComboBox list from a range in Excel VBA. In addition, we’ll also discuss how to add items to a ComboBox without VBA and insert elements into a ComboBox from a dynamic range in Excel.

What Is VBA ComboBox List?

First of all, let’s start with a quick explanation, so you don’t have to spend all day on this.
In a nutshell, a ComboBox consists of a list of text from which we can choose any item of our choice. Moreover, we also obtain the serial number of choices, by linking a cell that refers to this list.

How to Populate ComboBox List from Range in Excel VBA: 2 Ways

Now, let’s assume the Sales Dataset shown in the B4:D13 cells, which shows the “Brand”, “Model” and “Price” of laptops respectively. Here, we want to populate the ComboBox list from the range using Excel VBA Macros. Henceforth, let’s see each method in detail and with the necessary illustrations.

Here, we have used the Microsoft Excel 365 version; you may use any other version according to your convenience.

1. Using Named Range

In the first place, let’s start with the most popular way to add items to a ComboBox from a range using VBA.

📌 Steps:

• First, select the B4:D4 cells >> enter a suitable Named Range, for example, “Col_Headers”.

• Next, select the B5:B9 cells >> enter the name “Brand” in the Name Box >> likewise name the C5:C13 and D5:D13 ranges as “Model” and “Price” respectively.

• Second, go to the Developer tab >> click on Insert >> choose the ComboBox option.

• Then, left-click and drag the cursor to insert the ComboBoxes.

• Third, right-click on the worksheet name >> press the View Code button.

• In turn, copy and paste the code >> insert into the window as shown below.

Private Sub ComboBox1_Change()
    Dim rng As Range
    Set rng = Range(Me.ComboBox1.Text)
    Me.ComboBox2.List = Application.WorksheetFunction.Transpose(rng)
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim rng As Range
    Set rng = Range("Col_Headers")
    Me.ComboBox1.List = Application.WorksheetFunction.Transpose(rng)
End Sub

• Fourth, click the Design Mode toggle to switch it off.

• Finally, move to any other cell >> then click the drop-down arrow to choose items from the ComboBox.

2. Specifying Cell Range

Alternatively, we can also specify the cell range within the VBA code so that the text items in the ComboBox are included only from this array.

📌 Steps:

• At the very beginning, follow the steps shown in the previous method to insert the ComboBox >> in the Developer tab, click on the Visual Basic option.

• Following this, insert a Module from the Insert tab.

• At this point, enter the code into the Module window.

Sub Add_to_ComboBox()

Dim vData As Variant
Dim x As Integer

vData = WorksheetFunction.Transpose(Sheets(3).Range("C5:C13").Value)
With Sheets(3).OLEObjects("ComboBox1").Object
     .Clear
     For x = LBound(vData) To UBound(vData)
        .AddItem vData(x)
     Next x
End With

End Sub

• Lastly, close the VBA window >> click the Macros button.

This opens the Macros dialog box.

• Now, select the Add_to_ComboBox macro >> hit the Run button.

Eventually, the results should look like the image given below.

How to Utilize VBA to Generate ComboBox from Dynamic Cell Range in Excel

Besides, we can utilize the UserForm option to populate a ComboBox from a dynamic range with the help of VBA code. Now, this means that even if you add new rows or columns, the user form will include them in the drop-down list. Therefore, let’s see it in action.

📌 Steps:

• Initially, copy and paste the dataset in the A1 cell >> open the Visual Basic editor.

• Then, insert a UserForm as shown in the picture below.

• Next, select the Labels and ComboBox options to insert them into the UserForm.

• Second, choose any one of the ComboBox >> right-click to jump to the View Code option.

• In turn, enter the following code in the VBA window

Private Sub ComboBox1_Change()

Dim wksht As Worksheet
Set wksht = ThisWorkbook.Sheets("ComboBox from Dynamic Range")

Dim j, k As Integer
Me.ComboBox2.Clear

k = Application.WorksheetFunction.Match(Me.ComboBox1.Value, wksht.Range("1:1"), 0)

For j = 2 To Application.WorksheetFunction.CountA(wksht.Cells(1, k).EntireColumn)
    Me.ComboBox2.AddItem wksht.Cells(j, k).Value

Next j

End Sub

Private Sub UserForm_Activate()

Dim wksht As Worksheet
Set wksht = ThisWorkbook.Sheets("ComboBox from Dynamic Range")
Dim j As Integer

Me.ComboBox1.Clear

For j = 1 To Application.CountA(wksht.Range("1:1"))
    Me.ComboBox1.AddItem wksht.Cells(1, j).Value
Next j

End Sub

• Afterward, hit the F5 key or the Run button to execute the code.

• Consequently, this prompts the UserForm where we can choose a “Category” and get its respective “Information”.

Furthermore, adding a new column, in this case, “New Price” automatically adds it to the list.

How to Use VBA to Populate a ComboBox from Another Worksheet in Excel

Besides, we can also pull in data from another worksheet and add it to the ComboBox drop-down. Now, allow us to demonstrate the process in the steps shown below.

📌 Steps:

• To begin with, insert the VBA code into the Module as shown in the screenshot below.

In this situation, we’ll apply the ComboBox.List property to insert items into the ComboBox in Sheet 6 (“Another worksheet”) by extracting the text from the C5:C13 range in Sheet 1 (“Dataset worksheet”).

Private Sub Add_ComboBox_from_Another_Worksheet()
    Sheets(6).ComboBox1.List = Sheets(1).Range("C5:C13").Value
End Sub

• Later, hit the Run button or the F5 shortcut key.

Ultimately, the final output appears in the figure shown below.

How to Add a ComboBox from a Range of Cells in Excel (Without VBA Code)

Now, what if you want to avoid applying VBA code altogether? In the following section, we’ll answer this exact question. So, just follow along.

Utilizing Form Controls

For one thing, we can use the Form Controls option to insert a ComboBox and then use the Properties feature to specify the input cells.

📌 Steps:

• To start with, in the Developer tab, choose the Insert option >> select ComboBox from the Form Controls section >> add it to the worksheet.

• At this point, select the ComboBox >> hit the Properties feature >> in the Control tab, and highlight the C5:C13 range of cells.

Boom! That’s how simple it is to add a ComboBox list from a range of cells without any VBA code.

Utilizing ActiveX Controls

In a similar fashion, we can employ the ActiveX Controls to include items to a ComboBox from a range of cells.

📌 Steps:

• First and foremost, insert a ComboBox as shown previously >> proceed to the Properties option.
• Not long after, in the ListFillRange field enter the C5:C13 array.

Additionally, you can explore the differences between From Controls and ActiveX Controls in Excel, if you wish.

Read More: Excel VBA ComboBox: Important Properties to Explore

Practice Section

We have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.

Download Practice Workbook

Conclusion

In essence, this article shows 2 effective methods on how to populate ComboBox list from range in Excel VBA. So, read the full article carefully and download the free workbook to practice. Now, we hope you find this article helpful and if you have any further queries or recommendations, please feel free to comment here.

Related Articles

• Excel VBA ComboBox Value
• How to Use Excel VBA ComboBox with Control Source Property 
• Create ComboBox with RowSource in Excel VBA
• How to Get Excel VBA Combobox with RowSource Named Range