A ComboBox consists of a list of text from which we can choose an item.
In this sample Sales Dataset we want to populate the ComboBox list from the given range using Excel VBA Macros.
Method 1 – Using Named Range
Steps:
- Select the B4:D4 cells.
- Enter a suitable Named Range, for example, “Col_Headers”.
- Select the B5:B9 cells.
- Enter the name Brand in the Name Box.
- Repeat for C5:C13 and D5:D13 ranges with Model and Price respectively.
- Go to the Developer tab.
- Click on Insert.
- Choose the ComboBox option.
- Left-click and drag the cursor to insert the ComboBoxes.
- Right-click on the worksheet name and select the View Code button.
- Copy and paste the below code into the window.
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:
The code consists of two sub-routines.
- In the first sub-routine, enter a name, here it is ComboBox1_Change()
- Define the variable rng and assign it as Range.
- Use the Set statement to store the text values in the ComboBox_1.
- 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.
- Click the Design Mode toggle to switch it off.
- Move to any other cell and then click the drop-down arrow to choose items from the ComboBox.
Method 2 – Specifying Cell Range
Steps:
- Follow the steps shown in the previous method to insert the ComboBox.
- In the Developer tab, click on the Visual Basic option.
- Insert a Module from the Insert tab.
- Enter the below 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:
- Define the macro name, here it is Add_to_ComboBox().
- Assign Integer and Variant data types to the x and vData variables.
- Utilize the VBA Transpose function to flip from a horizontal to a vertical range.
- 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.
- Close the VBA window and click the Macros button.
- Select the Add_to_ComboBox macro and hit the Run button.
The results should look like the image below.
How to Utilize VBA to Generate ComboBox from Dynamic Cell Range in Excel
The UserForm option can populate a ComboBox from a dynamic range with the help of VBA code. This means that even if you add new rows or columns, the user form will include them in the drop-down list.
Steps:
- Copy and paste the dataset in the A1 cell then open the Visual Basic editor.
- Insert a UserForm.
- Select the Labels and ComboBox options to insert them into the UserForm.
- Choose any one of the ComboBox and right-click to jump to the View Code option.
- 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:
- Rename the first sub-routine which is ComboBox1_Change()
- Define the variable wksht, j, and k variables and assign the Worksheet object and Integer datatype.
- 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.
- Hit the F5 key or the Run button to execute the code.
- This prompts a UserForm to appear where we can choose a “Category” and get its respective “Information”.
In addition, adding a new column at a later date, in this case “New Price”, automatically adds it to the list.
How to Use VBA to Populate a ComboBox from Another Worksheet in Excel
Steps:
- Insert the below VBA code into the Module.
The ComboBox.List property allows users 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
- Hit the Run button or the F5 shortcut key.
The final output appears as below.
How to Add a ComboBox from a Range of Cells in Excel (Without VBA Code)
Utilizing Form Controls
Steps:
- In the Developer tab, choose the Insert option.
- Select ComboBox from the Form Controls section.
- Add it to the worksheet.
- Select the ComboBox and then the Properties feature.
- In the Control tab insert the C5:C13 range of cells.
Utilizing ActiveX Controls
Steps:
- Insert a ComboBox as shown previously and select the Properties option.
- In the ListFillRange field enter the C5:C13 array.
You can learn more about the differences between From Controls and ActiveX Controls in Excel here.
Read More: Excel VBA ComboBox: Important Properties to Explore
Download Practice Workbook
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
