Populate Combo Box with Data from Named Range in Excel

Adding combo boxes to your Excel spreadsheets will improve its functionality and usability by giving users a preset selection of alternatives to select from. You may simply alter the list of possibilities without changing any formulas or code by attaching a combo box to a specified range. In this article, we'll look at the procedures for filling a combo box with information from a named range. Regardless of your level of Excel proficiency, this article will show you how to maximise combo boxes' potential and speed up the data entry process.

It's imperative to have a fundamental understanding of Excel and its jargon before we start the training. It will be helpful to be familiar with terms like cells, ranges, and formulas. Furthermore, a version of Excel that supports form controls is required because we will be using the built-in form controls in Excel to create the combo box.

Populate Combo Box With Data of Named Range

Here we will first name the range of cells, then insert a combo box and add a VBA code to the sheet to complete the task. So let us see a simple process to know how you can populate a combo box with data from a named range in Excel.

Step 1

Consider an Excel sheet where the data in the sheet is similar to the below image.

First, Select the range of header cells and click on the name box. Then, enter the name as Head and click Enter.

Select Cells > Name Box > Head > Enter.

Step 2

Then name the cells of each column with their header value by following the above step.

Step 3

Then click on Developer, click on Insert, and select the combo box under ActiveX controls.

Developer > Insert > Combo Box.

Step 4

Then draw two combo boxes. Then right-click on the sheet name and select View Code to open the VBA application, then copy the below code to the text box.

Draw > Right Click > View Code > Copy.

Code

Private Sub ComboBox1_Change()
   Dim xRg As Range
   Set xRg = Range(Me.ComboBox1.Text)
   Me.ComboBox2.List = Application.WorksheetFunction.Transpose(xRg)
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   Dim xRg As Range
   Set xRg = Range("Head")
   Me.ComboBox1.List = Application.WorksheetFunction.Transpose(xRg)
End Sub

Step 5

Then close the VBA using Alt + Q and exit the design mode to complete the task.

Conclusion

In this tutorial, we have used a simple example to demonstrate how you can populate a combo box with data from a named range in Excel to highlight a particular set of data.