Eshrak Kader
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.
Download Practice Workbook
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.
2 Ways to Populate ComboBox List from Range in Excel VBA
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. 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.
- 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
⚡ 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.
- 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
⚡ 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.
- 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.
Read More: How to Select First Item from ComboBox Using VBA in Excel
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
⚡ 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.
- 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.
Read More: How to Add ComboBox in Excel (3 Easy Methods)
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: How to Use ListFillRange Property of ComboBox in Excel
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.
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. Lastly, visit ExcelDemy for many more articles like this.
