You can specify a Combo box [ActiveX Control] with data of a certain range by adding the range into the ListFillRange properties of the combo box. But do you know how to populate a Combo box with data of a named range in Excel? For example, you have named a range of cells in your worksheet, and now, need to specify this range name into a Combo box. When selecting the range name in one combo box, all cell values in this named range will be populated in another Combo box automatically. This article will introduce a VBA method to solve this problem.
Populate Combo box with data of a named range with VBA code
Populate Combo box with data of a named range with VBA code
Please do as follows to populate Combo box with data of a named range in Excel.
1. Please select the whole headers [in this case, I select A1:E1] in your worksheet, and then type a name into the Name Box as below screenshot shown.
2. Select each column data except its header, and then name the column separately in the Name Box. See screenshot:
In this case, I named range A2:A8, B2:B8, C2:C8, D2:D8 and E2:E8 as Date, Shop, Items, Sales and Weekday separately.
3. Then insert a Combo box by clicking Developer > Insert > Combo Box [ActiveX Control]. See screenshot:
4. Repeat the step 3 to insert another Combo box into the worksheet.
5. Right-click the sheet tab, and then click View Code from the context menu. See screenshot:
6. In the opening Microsoft Visual Basic for Applications window, please copy and paste below VBA code into the Code window.
VBA code: Populate Combo box with data of a named range
Private Sub ComboBox1_Change[] 'Updated by Extendoffice 2018/1/30 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["Headers"] Me.ComboBox1.List = Application.WorksheetFunction.Transpose[xRg] End SubNote: In the code, ComboBox1 is the name of the combo box which you will list all range names inside, and ComboBox2 is the name of the combo box which the data of specified named range will be populated inside. The”Headers” is the range name you have created in step1.
7. Turn off the Design Mode by clicking Developer > Design Mode.
8. Click on any cell in the worksheet to activate the code. Click the arrow button in the first combo box, you can see all named ranges are listed inside. See screenshot:
When selecting a named range in the first combo box, the corresponding cell data will be populated in the second combo box as below screenshot shown:
Related articles:
No ratings yet. Be the first to rate!
Populating a combo box isn't hard, but getting the combo box to communicate with a constantly changing list is a little more difficult. Learn how to populate your combo box with a dynamic list. Populating a userform combo box with a static list takes a little knowledge and some VBA code. Getting the combo box to update when the list updates requires a bit more work. You could update the list range every time you update it or you could create a dynamic list. By dynamic list, I really mean a dynamic range that contains a list of data items. Let’s take a look at a quick example. The following userform contains one combo box, with an identifying label. You want to populate the combo box using the list in A1:A6 [on a sheet named LookupLists, which isn’t shown in the figure].
The first step is to create a dynamic range for the list as follows:
- Click the Formulas tab and then click Define Name in the Defined Names group to open the New Name dialog box. In Excel 2003, choose Name from the Insert menu and then select Define.
- Enter a name for the range, ColorList.
- In the Refers To control, enter the following expression: =OFFSET[LookupLists!$A$2, 0, 0, COUNTA[LookupLists!$A:$A]-1,1]. LookupLists is the name of the sheet that contains the list of colors.
- Click OK.
When adapting the expression to your own work, don’t include a header cell in the range [LookupLists!$A$2]. Identify just the cells that contain actual list items. In addition, both cell references must be stated as absolute.
Next, create the userform as follows:
- Open the Visual Basic Editor [VBE] by pressing [Alt]+[F11].
- From the Insert menu, choose UserForm.
- Using the Toolbox, insert a combo box control. The Toolbox should be visible when you select the userform. If necessary, choose Toolbox from the View menu.
- I added a label control and entered the text Color. You can skip this step if you like.
- Name the combo box control cboColor.
Now you’re ready to add the code that populates the combo box. Do so as follows:
- Double-click the userform to open its module.
- Enter the sub procedure shown below:
'Populate Color combo box.
Dim rngColor As Range
Dim ws As Worksheet
Set ws = Worksheets["LookupLists"]
For Each rngColor In ws.Range["ColorList"]
Me.cboColor.AddItem rngColor.Value
Next rngColor
End SubTo see how the combo box works, return to the userform [close the module] and run it by clicking [F5]. The For Each statement populates the list using the data items in the range ColorList. Close the userform.
Now, let’s add an item to ColorList and see how well the combo box performs. Return to the sheet that contains the list and enter White in cell A7. Then, return to the VBE and run the userform a second time. As you can see, the range name ColorList automatically adapts to include the new list item, White. Subsequently, the code populates the combo box with the entire list, including White, without any modifications.
In order to keep the example simple, I’ve bypassed any data entry task. In a real-world application, you’d probably want to copy the value selected by the combo box to a data range. That requires additional code. I’ve only shown you how to populate the combo box with the contents of a dynamic range [list].