Excel VBA to Populate ComboBox from Dynamic Range

Get FREE Advanced Excel Exercises with Solutions!

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.

VBA Populate Combobox from Dynamic Range


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.

Use ActiveX Controls for Populating ComboBox from Dynamic Range

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

Use ActiveX Controls for Populating ComboBox from Dynamic Range

  • 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, rightclick 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

Use ActiveX Controls for Populating ComboBox from Dynamic Range

In this code, ComboBox1 holds the range cames and ComboBox2 holds the data of each range. The Col_Headers is the header range name that we created earlier.
  • 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.

VBA Populate Combobox from Dynamic Range


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.

Populate ComboBox from Dynamic Range with VBA UserForm

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

Populate ComboBox from Dynamic Range with VBA UserForm

  • 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, rightclick 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

Populate ComboBox from Dynamic Range with VBA UserForm

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.

Populate ComboBox from Dynamic Range with VBA UserForm

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

Populate ComboBox from Dynamic Range with VBA UserForm

  • Even, you can insert a new column and get relevant data in the UserForm.

Read More: How to Use VBA to Populate ComboBox List from Range in Excel


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.


Conclusion

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.


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:

Sanjida Mehrun Guria
Sanjida Mehrun Guria

Hello! Welcome to my blog. I have completed my Bachelor in Architecture from Ashanullah University of Science & Technology, Dhaka. I am a passionate, goal-oriented person with an immense thirst for knowledge and an attitude to grow continuously. Besides Architectural work, I find it very enthusiastic to work in Excel blogging. Exceldemy is a platform where I have got the opportunity to flourish my skill in Microsoft Excel and therefore, here I will be posting blogs related to... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo