Software

Populate a userform combo box in Excel with a dynamic list

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:

  1. 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.
  2. Enter a name for the range, ColorList.
  3. 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.
  4. 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:

  1. Open the Visual Basic Editor (VBE) by pressing [Alt]+[F11].
  2. From the Insert menu, choose UserForm.
  3. 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.
  4. I added a label control and entered the text Color. You can skip this step if you like.
  5. Name the combo box control cboColor.

Now you're ready to add the code that populates the combo box. Do so as follows:

  1. Double-click the userform to open its module.
  2. Enter the sub procedure shown below:

Private Sub UserForm_Initialize()

'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 Sub
To 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).

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

11 comments
david.ronka
david.ronka

This is a great thread with a lot of helpful ideas. The list I'm using to populate my userform combo box has duplicate items in it. I'd like the userform list to pull a list of unique values from that list. Anyone know how to add that twist?

nanc60
nanc60

I am trying to follow the explanation but when i am trying to run the userform, i am getting this: Run-time error '9' Subscript out of range. Help?

ssharkins
ssharkins

That would definitely work -- it requires more code, which is something I tend to avoid when offering solutions, but in the long run might be more manageable as you say -- thanks for sharing this solution!

DBlayney
DBlayney

The solution is, for me, a bit complex. The RefersTo formula to define the list is somewhat less than transparent. It is also important that column A of the Sheet ColorLists is not used for anything other than the list items because the expression COUNTA(ColorLists!$A:$A) would no longer count only the list entries. When I have done somethign similar I have, as DuhGreek suggests, used the RowSource property to define the combo box items. The range ColorList can be defined simply as ColorLists!$A$2:$A$6. A data entry rule is required that new list entries are made by using Insert Cells somewhere between the first and last entries of the list so that the range automatically adjusts to the new number of entries.

cfolster
cfolster

How do you copy the value selected by the combo box to a data range?

techrepublic
techrepublic

=OFFSET(LookupLists!$A$2, 0, 0, COUNTA(LookupLists!$A:$A)-1,1) is working. The colon was missing in the COUNTA range

marialcorn
marialcorn

I think that the 2nd LookupList cell ref should be $a$2 same as first one?

owner78
owner78

sorry but it gives me error in =OFFSET(LookupLists!$A$2, 0, 0, COUNTA(LookupLists!$A$A)-1,1) and at run time gives range error

AnantL
AnantL

try changing sheet name to Lookuplist. . it is mentioned in the article to set the sheet name containing look up list to Lookup list.

DuhGreek
DuhGreek

You can either assign the value to a variable or you can make a direct assignation to a specific cell (by address or name): Range("C5") = Me.cboColor.Value ... and a simpler way of populating the list is the enter the range name (ColorList) in the "RowSource" property of the combo box. You can also assign it using VBA: cboColor.RowSource = "ColorList"

ssharkins
ssharkins

Nice catch -- thank you for helping out. I've corrected the expression in the article.

Editor's Picks