When we work with a large dataset, we often need to find some specific values under certain categories. But it gets really difficult to just scroll and find it. For this, Microsoft Excel has introduced ComboBox to solve this problem. In this article, we will learn in 2 unique ways how to use VBA to populate a ComboBox from a dynamic range in Excel.
What Is ComboBox in Excel?
ComboBox is a form of listing data in Excel. It is quite similar to the general drop-down list or data-validated ListBox in VBA. ComboBox allows its users to input and choose data from a selected range or multiple ranges. It can be created directly through VBA code or VBA UserForm in Excel.
How to Use VBA to Populate ComboBox from Dynamic Range in Excel: 2 Unique Ways
To describe the methods, here is a sample dataset. This shows the information of Category, Product and Sales amount of 10 types of items in a store.
Assuming this as a long dataset, we will therefore populate a ComboBox from this dynamic range with VBA to fetch specific information.
1. Use ActiveX Controls for Populating ComboBox from Dynamic Range
In this first method, we will insert ComboBox from the ActiveX Controls in the Excel Ribbon. To get into the detailed process, go through the steps below.
- In the beginning, select the headers of the dataset and name the range Col_Headers in the Name Box.
- Then, select the Cell range B5:B7 and name it Category.
- Similarly, name the Cell range C5:12 and D5:C12 as Product and Sales respectively.
- Now, go to the Developer tab and click on Insert.
- Here, choose ComboBox from the ActiveX Controls section.
- Then, drag your cursor on the worksheet and create a box like this.
- Similarly, create another ComboBox and give a header to each according to your preference.
- Lately, it will look like this.
- Next, right–click on the active worksheet and select View Code.
- Afterward, insert this code on the Code page.
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
- Lastly, turn off the Design Mode from the Developer tab.
- That’s it, you will get your required ComboBoxes with a dynamic range.
- Simply choose any option from the Topic ComboBox and relevant data will generate in the Data ComboBox.
2. Populate ComboBox from Dynamic Range with VBA UserForm
Another method of populating ComboBox for dynamic range is to create an UserForm in the VBA editor. Let’s see how it works.
- First, press Ctrl + C to copy the dataset and press Ctrl + V in a new worksheet to paste it into Cell A1.
- Second, open Microsoft Visual Basic for Applications by pressing Alt + F11 on your keyboard.
- Here, select UserForm from the Insert tab.
- Then, create Labels and ComboBoxes in the UserForm by dragging from the Toolbox.
- Along with it, change the Properties of each Label box according to your preference.
- For this, press F4 and you can get the Properties Window.
- Next, right–click on any of the ComboBoxes and select View Code.
- Following this, insert this code on the Code page.
Private Sub ComboBox1_Change() Dim wksht As Worksheet Set wksht = ThisWorkbook.Sheets("UserForm") 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("UserForm") 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
In this code, we provided Range (“1:1”) for integer k as our dataset starts from Cell A1. then, we typed integer j=2 as the values start from Row 2 after the header. Along with it, “UserForm” is the worksheet name where we are applying the code.
- Now, click on the RunSub button on press F5 on your keyboard.
- As a result, you will get the UserForm1 popup window that we created earlier.
- Finally, you will get the headers and their relevant data in drop-down lists under ComboBoxes.
- Now, you can choose values from the dynamic ranges.
- Even, you can insert a new column and get relevant data in the UserForm.
Things to Remember
- When you name any range, make sure to insert the same name that the range holds in the header.
- If your dataset has any repetitive value then during selecting a range to name, ignore the repeated valued cells.
- If the practice file does not work, then Unblock > Apply > OK from its Properties to enable all macros.
Download Practice Workbook
Get the sample file and practice by yourself.
Finally, we are at the end of our article. Here we learned how to use VBA to populate ComboBox from dynamic range in Excel through 2 unique ways. Let us know your feedback in the comment box.