Populating One-Column ListBox/ComboBox

Listboxes and Comboboxes are ActiveX controls that you put on a Userform. You can also put them directly on worksheets, but this post will be in the context of Userforms.

There are also Forms Listboxes and Comboboxes. The Forms controls are accessed from the Forms toolbar, while the ActiveX controls are accessed from the Control Toolbox. You cannot put Forms controls on a Userform, only ActiveX controls.

For these examples, I’ll be using a range on Sheet1 like this:

listbox1

Both Listboxes and Comboboxes have a RowSource property. This property takes a string that evaluates to a range address. To populate a listbox with the first column of the above range, set the RowSource property to look like this (Select the listbox and press F4 to display the properties dialog):

listbox2

You can also name the range and use that name in the Rowsource property. If you named A2:A8 “DaysList”, you could set the RowSource property to Sheet1!DaysList to get the same result.

It is also possible to set the RowSource at run time. The above example is setting it at design time. I generally use the Initialize Event when I want to manipulate controls. It fires whenever the Userform is loaded.

Private Sub UserForm_Initialize()
    Me.ListBox1.RowSource = "Sheet1!DaysList"
End Sub

The difference between setting this property at run time vs. design time is the persistence of the property. When it’s set at design time, the property holds its value. At run time, it doesn’t which means that you have to set it each time you load the Userform.

I have never used the RowSource property. I have fundamental problems with connecting a Userform to a Worksheet. If the name of the worksheet changes, the RowSource property will not populate the ListBox or it will populate the wrong data. The RowSource property is a string, not a range, so it doesn’t adjust as you mess with your ranges on the Worksheet.

I exclusively use the AddItem method to populate controls. To me, this affords maximum flexibility. If I want to show the range a little differently, I can do that without changing what’s displayed on the worksheet. This comes in handy when working with dates. Again using the Initialize event, here’s how I would populate the ListBox:

Private Sub UserForm_Initialize()
    Dim cell As Range
    Dim Rng As Range
   
    With ThisWorkbook.Sheets("Sheet1")
        Set Rng = .Range("a2", .Range("a2").End(xlDown))
    End With
   
    For Each cell In Rng.Cells
        Me.ListBox1.AddItem cell.Value
    Next cell
   
End Sub

Note that if the RowSource property is set, you will get an error trying to use AddItem. It’s one or the other, not both.

If I now decide that I want to abbreviate the names that I add to the ListBox, I can do that with my AddItem code. This is where AddItem really shines. I can replace one line of the above code, like this

Me.ListBox1.AddItem Left(cell.Value, 3)

and get this as a result

listbox3

And I didn’t have to mess with the data on the worksheet.

There are plenty of people that use RowSource, so don’t take my idiosyncrasies to heart. Now you know both ways and you can decide for yourself.

17 Comments

  1. Jake Marx says:

    Dick,

    Just a quick comment that I’m sure you’re aware of already – you can populate the list much more quickly by using the List property. Here’s an example:

        With Sheet1
            ListBox1.List = Application.Transpose(.Range(.Range("A2"), .Range("A2").End(xlDown)).Value)
        End With
    

    Note that this won’t work reliably with longer lists in versions earlier than XL 2000 due to the limitation of the TRANSPOSE worksheet function.

  2. Dick says:

    Jake: Yeah, you’d think so. Everytime I see that method I think “I should start using that.” Then by the time I need it, I’ve already forgotten about it. Thanks for posting it.

  3. Interested Excel Dabbler says:

    So, if I were to take the breast enlargement pills at the same time as the penis enlargement pills, would it then be easier for me to find work playing with Excel? :-)

  4. ^_^ says:

    lol….. -_-

  5. ju says:

    hi

    And if you just want to show column A and C in a listbox
    Is it possible?

  6. Jon Peltier says:

    ju:

    I would pull the range A:C into a VB array, use it to populate a 2-column array, then use this array in the .List property of the ListBox. Something like this:

    vArrayOrig = Worksheets(“Sheet1?).Range( _
    Worksheets(“Sheet1?).Range(“A1?),Worksheets(“Sheet1?).Range(“C1?).End(xlDown)).Value

    Redim vArrayList(lbound(vArrayOrig, 1) to ubound(vArrayOrig, 1), 1 to 2)

    For i = lbound(vArrayOrig, 1) to ubound(vArrayOrig, 1)
    vArrayList(i, 1) = vArrayOrig(i, 1)
    vArrayList(i, 2) = vArrayOrig(i, 3)
    Next

    ListBox1.List = vArrayList

  7. Juan Pablo Gonzalez says:

    Or you can use the ColumnWidths property to hide column B:

    Dim vArrayOrig As Variant

    With Worksheets(“Sheet1?)
    vArrayOrig = Range(.Range(“A1?), .Range(“C1?).End(xlDown)).Value
    End With
    ListBox1.ColumnCount = 3
    ListBox1.ColumnWidths = “;0;”
    ListBox1.List = vArrayOrig

  8. ju says:

    great, thank you

  9. zack says:

    does anyone know how I would go about making the rowsource property of one combobox dependant on the selection made in another combo box?
    For Example:

    if option A in combobox1 is chosen
    then rowsource property of combobox2 is sheet3!c2:c6
    but if option B in combobox1 is chosen
    then rowsource property of combobox2 changes to sheet3!d3:d6

  10. Matt says:

    Hi Dick

    Thanks for all the information you make available on your site.

    How does your approach remove the connection to the spreadsheet? True the range it refers to is more dynamic and as you demonstrate it is easier to manipulate how the data is viewed in the listbox but if the user renames Sheet1 it still fails. I can see the benefit of your approach but I am not sure how it addresses your statement “I have fundamental problems with connecting a Userform to a Worksheet”?

    Regards
    Matt

  11. Matt: Yeah, that’s a bad example I posted. I should have used the sheet’s codename, which doesn’t change when the sheet’s name is changed.

  12. Hans Schraven says:

    I prefer this method for a 1-column combobox/listbox; fast, simple and flexible

    Private Sub UserForm_Initialize()<br>
      sq = Sheets(1).UsedRange.Columns(4)<br>
      ComboBox1.List = sq<br>
    End Sub[VB]

  13. Jon Peltier says:

    Matt -

    A better approach is to use something like this to get the contents of a named range, all at once into a variant, and assign it to the listbox:

    Private Sub UserForm_Initialize()
        Dim vListSource As Variant
       
        With ThisWorkbook.Names(“ListSource?)
            vListSource= .RefersToRange.Value
        End With
       
        Me.ListBox1.List = vListSource
       
    End Sub

    To ensure you only get one column, you can use:

            vListSource= .RefersToRange.Columns(1).Value

    and there are other validation steps you may want to take, such as sorting, removing blanks, or other filtering. I find they may be faster and easier to do in VBA on the variant array vListSource.

    Hans -

    Your approach is essentially the same except (1) you have not declared your variable sq, which must be a variant, and (2) for completeness (anal retentive? moi?) you ought to say

      sq = Sheets(1).UsedRange.Columns(4).Value
  14. Hans Schraven says:

    @Jon

    I knew someone would ‘correct’ me.
    But alas, I refrain from adding redundancies and defaults.
    And as you already noticed: the point was ‘filling comboboxes/listboxes’ with one column.
    But thanks for your attentiveness.

  15. Jon Peltier says:

    What is the redundancy, declaring sq as a variant? You haven’t declared it at all, and if you don’t have Option Explicit stated atop the module, you’re in danger of accidentally typing the variable qs instead of sq, and the code will seem to run but fail.

    Placing the default in the statement makes it better self-documenting. For example, the code knows that

    Sheets(1).UsedRange.Columns(4)

    refers to the values in the range, but I might read it quickly, not notice the absence of Set, and think it references the range itself.

    These are just my habits and rationale behind them. YMMV.

  16. Hans Schraven says:

    Jon,

    Thanks for explaining your habits.

    To explain mine
    sq = [A1:D1]
    c0 = VarType(sq)
    The vartype of sq is 8204: a variant(8192) consisting of an array(12).
    Declaring a variable as Variant that the VBA-interpreter declares to be a variant seems redundant to me.

    The preferred method would have been (without any variables at all):
    ComboBox1.List = Sheets(1).UsedRange.Columns(4)
    But that doesn’t work.
    Not even
    ComboBox1.List = Cvar(Sheets(1).UsedRange.Columns(4))

    Luckily we know that this functions correctly:
    ComboBox1.List = WorksheetFunction.Transpose(Sheets(1).UsedRange.Columns(4))

    To prevent mistyping and misreading I regularly let my contactlenses being adjusted. ;)

    YC.

  17. Jon Peltier says:

    Hans -

    I stick with my suggestion that you fully reference your objects. Don’t be lazy and use [A1:D1] when what you mean is ActiveSheet.Range(“A1:D1?).Value

    Declaring a variable as a variant is not being redundant. If you declare it with no type, Excel assigns its default. That’s not really the same. Doesn’t it help anyway to actually see how things are declared?

    Your code

    ComboBox1.List = Sheets(1).UsedRange.Columns(4)

    may not work for any number of reasons.

    Most likely it’s because TypeName(Sheets(1).UsedRange.Columns(4)) is “Range”, not “Variant()”. So is TypeName(CVar(Sheets(1).UsedRange.Columns(4))), try it.

    Using CVar in this case merely changes the range into a variant that contains a range. It’s so simple to use .Value to turn the range into a variant array, that refusing to use it merely because it’s the default is silly.

    As you see, when it’s not always treated as the default, leaving it off does come back to bite you. You got lucky with Transpose, because that actually coerces the range into an array, but it’s an inefficient way to do so.

    A few minor adjustments, including the use of the .Value default and the use of qualifiers for the references:

    Private Sub UserForm_Initialize()
      Me.ListBox1.List = ActiveWorkbook.Worksheets(1).UsedRange.Columns(4).Value
    End Sub

    makes the code work just fine.

    Note that I used a listbox so I could see the contents without clicking on the dropdown arrow. This is a best practice if you have room on your dialog, even though the designers of Excel 2007’s new dialogs threw away lists in favor of combos in so many places.

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: