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

Get FREE Advanced Excel Exercises with Solutions!

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.

Dataset for excel vba combobox list from range

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”.

Using Named Range and VBA Code

  • 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.

Using Name box

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

Inserting ComboBox

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

Click on View Code

  • 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

excel vba combobox list from range code

Code Breakdown:

Here, we’ll explain the VBA code used to populate the ComboBox list from a range in Excel VBA. In this case, the entire code consists of two sub-routines.

  • In the first sub-routine, enter a name, here it is ComboBox1_Change()
  • Next, define the variable rng and assign it as Range.
  • Then, use the Set statement to store the text values in the ComboBox_1.
  • Now, apply the VBA Transpose function to convert the horizontal range to a vertical range.
  • In the second sub-routine, set the rng variables to the Named Range “Col_Headers” and apply the VBA Transpose function to convert rows to columns.

Code explanation

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

Turn off Design Mode

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

excel vba combobox list from range using Named Range


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:

Specifying Cell Range within VBA Code

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

Inserting Module

  • 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

excel vba combobox list from range Specifying Cell Range

Code Breakdown:

  • First, define the macro name, here it is, Add_to_ComboBox().
  • Next, assign Integer and Variant data types to the x and vData variables.
  • Now, utilize the VBA Transpose function to flip from a horizontal to a vertical range.
  • Later, combine a For loop and the AddItem method to iterate through the range of cells and add the text to the variable.

📃 Note: Please make sure to enter the correct Sheet Number when using the VBA code. In this case, Sheets(3) pertains to the “Specifying Cell Range” which is the third worksheet.

Code explanation for excel vba combobox list from range Specifying Cell Range

  • 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.

Running Macro

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

excel vba combobox list from range by specifying cell range


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.

How to Populate ComboBox from Dynamic Range with VBA

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

Inserting UserForm

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

Adding Labels and ComboBox

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

Go to ComboBox View Code

  • 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

excel vba combobox list from range vba code

Code Breakdown:

  • To begin with, rename the first sub-routine which is ComboBox1_Change()
  • Next, define the variable wksht, j, and k variables and assign the Worksheet object and Integer datatype.
  • Then, use the Match method and For loop to iterate through all the values in the range, adding them to the ComboBox.
  • In the later sub-routine, employ the CountA method with a For loop to count the total number of non-blank cells within each column.

Code explanation

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

Pressing Run button

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

excel vba combobox list from range

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

excel vba combobox list from dynamic range


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

How to Populate a ComboBox from Another Worksheet

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

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

How to Populate a ComboBox from Another Worksheet in excel vba combobox list from range


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.

Utilizing Form Controls

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

Using Format Object dialog box

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

How to Add to a ComboBox from a Range of Cells Without VBA Code using Form Controls


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.

How to Add to a ComboBox from a Range of Cells Without VBA Code Utilizing ActiveX Controls

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.

Practice Section


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Eshrak Kader
Eshrak Kader

Eshrak Kader is a dedicated professional with a BSc. Degree in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. He boasts a rich background in Microsoft Office Suite, and over the past year, he has authored over 120 insightful articles for the ExcelDemy. Currently, Eshrak leads a team of 5 in the ExcelDemy Video project and enjoys problem-solving and making videos on Excel. Eshrak’s passion for continuous learning underscores his commitment to excellence in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo