Populating Multi-Column ListBox/ComboBox

Posted on by Dick Kusleika

In a previous post, I discussed populating one-column ActiveX controls with the RowSource property and the AddItem method. This post will discuss populating these controls when you want more than one column. Here’s the range that I am using

listbox1

To get all three columns in my listbox, I can set the RowSource property to

Sheet1!A2:C8

and be sure to set the ColumnCount property to 3.

To populate multi-column controls with the AddItem method, I use a procedure like this

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
With Me.ListBox1
.AddItem cell.Value
.List(.ListCount - 1, 1) = cell.Offset(0, 1).Value
.List(.ListCount - 1, 2) = cell.Offset(0, 2).Value
End With
Next cell

End Sub

This procedure loops through the cells in column A. It uses AddItem to add the column A value to the ListBox. Then it uses the List property to access the other columns. List takes a row and a column argument. The row argument is computed with the ListCount property. Because the rows and columns start at 0, but ListCount doesn’t, I have to subract one to get the right row. Also note that the second column is 1 and the third is 2.

With multi-column controls, the RowSource property starts looking pretty nice. It’s a heck of a lot easier than all this List and ListCount nonsense. Nevertheless, I still use AddItem exclusively, even for multi-column controls.

35 thoughts on “Populating Multi-Column ListBox/ComboBox”

  1. I have created a listbox on a form and have assigned a rowsource(A2:C10), but I am trying to create a rowsource that will find specific data from the spreadsheet. For example if I had a spreadsheet with Name, Gender, Age as the headings in row A1, on my form I would be able to create a listbox that showed all the Names for people who were 26.

    Can anyone help?

  2. For your criteria I had a similar problem, and here is my solution:

    For Each cell In Rng.Cells
    If cell.Offset(0, 3).Value = intAge Then
    With Me.ListBox1
    .AddItem cell.Value
    .List(.ListCount – 1, 1) = cell.Offset(0, 1).Value
    .List(.ListCount – 1, 2) = cell.Offset(0, 2).Value
    End With
    End If
    Next cell

    Good luck.

  3. Hi all

    I need to get the value of an m*n array from a combobox. I need to get a particular cell value say i and manipulate the value of i in some other formulae / case. For that case, to get the value of say second column fifth row how do i do that. Kindly help

    With regards

    Selva

  5. I don’t find RowSource too attractive. To populate my list boxes and combo boxes, single or multiple column, I usually use something like this:

    Dim vList as Variant

    With ThisWorkbook.Sheets(“Sheet1?)
      vList = .Range(“A2?, _
    .Range(“A2?).End(xlDown).End(xlToRight))
    End With

    Me.ListBox1.List = vList

    Easier than AddItem, and I can use a VB array for vList which I’ve manipulated independent of the worksheet.

  6. Please , im portguese a dont speek good english, but i would like ask for you a code that allowes to copy cells from list according the start cell, like this

    if cell starts from 11* in list column A , copy range A to C on that area and create sheet named A

    I would apreciat to listen same from you

  7. I was going to post about mutlicolumn combos, but I see it has been done.

    The following is the code I wrote:

    Create a new UserForm and drop a ComboBox and a Command Button onto it:

    Then use this code.

    Private Sub UserForm_Initialize()
        Init_ComboBox1
    End Sub

    Sub Init_ComboBox1()
        Dim rng As Range

        With ComboBox1
            .Clear
            .ColumnWidths = “;0”
            .ColumnCount = 2
            For Each rng In Range(“A2:A10”)
                .AddItem rng.Value
                .List(.ListCount – 1, 1) = rng.Offset(, 1).Value
            Next
        End With
    End Sub

    Private Sub CommandButton1_Click()
        With ComboBox1
            If .ListIndex > -1 Then
            MsgBox “You selected ” & .List(.ListIndex, 0) & ” ” & .List(.ListIndex, 1)
            End If
        End With
    End Sub

    Cheers,
    Rob

  8. Rob –

    Instead of this:

    For Each rng In Range(“A2:A10?)
    .AddItem rng.Value
    .List(.ListCount – 1, 1) = rng.Offset(, 1).Value
    Next

    How about this:

    .List = Range(“A2:B10?).Value

  11. Rob –

    It doesn’t matter so much now, but back when I was learning Excel VBA in Excel 97 on a rather slow Windows 95 laptop, I learned that looping cell-by-cell can seriously impact the speed of a procedure; this is even more pronounced when writing to the sheet than reading from it. Your snippet takes 18 trips from VBA to the worksheet to gather data, mine takes only one.

    – Jon

  12. Jon:

    My code was originally for looping a recordset. I just converted it to use ranges, for the sake of a reproducible, easy to follow, post.
    It never occurred to me that there might be a more elegant way with ranges.

    Indeed, I’ve noticed the “cell-by-cell” slowdown when writing – sometimes it seems slow even when calculations are set to manual.

    Rob

  14. Can you provide me the code that when I select a combo which list each sheets in a file will produce result containing the items in column a of the sheet selected into a listbox and when you select an item in that listbox, it will produce items into another listbox.

  15. Kobie, just a tip: Try reading your text before you post — from the perspective of someone who has no idea what you’re talking about. Try it with what you just posted, and you’ll realize why you will never get an answer.

  18. You can get any number of columns in a listbox or combobox (well, there must be a limit, but I’ve never exceeded it). Check the ColumnCount property. Note that the first column is column 0. Sometimes I’ll use extra columns to hold extra information, and give the column a width of zero so only I and my code know it’s there.

  19. Hello,
    I have a problem with listbox and combobox. I select an empty cell in excel sheet. I have form with listbox and comcobox. In other sheet I have a database – 16 colomn, or 8 pairs – caption of material and value for it. The caption in the first row define the sort of materials in the colomn situated in listbox. When some record is selected from the listbox, in the neighbour combobox, we have to show the other records from the choiced colomn. When we choice a record from the combobox, in the selected cell have to be placed the record from the combobox, and in the neighbour cell from the selected, we have to place the value of this record toward the combobox. Example:

    1 row: circle / value / squade/ value
    2 row: radius 20 / 20/ squade 20 / 400
    3 row: radius 30 / 30/ rectangle / 30

    listbox: circle, squade
    combobox1: radius 20, radius 30 (compared circle)
    comcobox2: squade 20, rectangle (compared squade)

    expected value for selected cell: combobox value
    next to selected cell: the value for selected combobox (400 for selected squade 20)

    I hope somebody to write the code that can relate the combobox value with some value from the excel table. 10x in advance

  20. Thanks Jon for the code below:
    Dim vList as Variant

    With ThisWorkbook.Sheets(“Sheet1?)
    vList = .Range(“A2?, _
    .Range(“A2?).End(xlDown).End(xlToRight))
    End With

    Me.ListBox1.List = vList

    It worked great for my project ( with minor changes, of course).

    Pam

  21. I am hoping you can help as I am not finding the direction I need.
    In the code below I do not understand how to seperate the information being retreived from the registry into seprate columns, hence I get a list that is twice as long (duplicates). In this case the registry values being retreived are “Key” and “Setting” which share the same values, IE, Key = Setting and Setting = Key.
    How can I get this list to be either two columns or a single column with no duplicates?

    ‘===========
    With ListBox1
    Dim c As Variant
    Dim sCustomer As Variant, iSettings As Integer
    sCustomer = GetAllSettings(appname:=”ADMCustomerList”, section:=”Customers”)
    For iSettings = LBound(sCustomer, 1) To UBound(sCustomer, 1)
    Debug.Print sCustomer(iSettings, 0), sCustomer(iSettings, 1)
    Next iSettings
    For Each c In sCustomer
    .AddItem c
    Next
    End With
    ‘=============

    Best Regards

    Rick
    XP Pro, XL2007

  22. I think I stumbled, and I don’t use that word lightly, on to an answer.


    '===========
    With ListBox1
    .Clear
    Dim c As Variant
    Dim x As String 'add this variable 10.26.07

  24. I’m struggling with some VBA code/logic (I am a bit of a newbie) to do the following:

    1/ Use the value selected from one listbox to limit the values displayed in another list box (similar to how a filter would limit the display of rows to only those having the first selected value)
    2/ The 2nd listbox needs to have its items added dynamically based upon the item selected from the 1st listbox
    3/ The 2nd list box should only add unique items … no duplicates in the list of items in the 2nd list box

    I need this logic to apply to 4 list boxes in total such that the items in listbox 2 are limited by the value selected in listbox one and the items in listbox 3 are limited by the value selected in listbox 2 and, finally, the items in listbox 4 are limited by the value selected in listbox 3.

    All list boxes are sitting in cells of a spreadsheet … not in a userform.

    Any advice/code most appreciated! Thanks!

  25. Please help,

    I am trying to populate a 4 column list box with a large amount of data from a seperate sheet (“Portfolio”), ideally I would like the list box to keep cylcling through the main list until it reaches the end (blank line) then enter the cell values into the list box accordingly.

    Just cant figure it out so any help would be much appreciated!!

    Thanks in advance

  26. I am glad there are a combination of methods here because I found that I really needed more than one method. I had a series of listboxes each with one less item than the others (process of eliminating the selected item from predecessor listbox, before putting remaining items in the next listbox, and so on)… but by the time I got to the last listbox I could not add 2 columns as an array using the list property. I tried every variation on this. ultimately I gave up and when ubound(MyArray,2) = 1 (i.e., which happened only on the last listbox which had but one choice to offer – I know, a dumb interface) I used .AddItem instead and the .List property for the other columns. Thanks to everyone here!

  27. Hi

    Feel like something is staring me in the face but I just can’t get my routine to work properly. If I try to add an 11th column to my listbox.

    I get an error message stating that “Error 380 Could not set the list property. Invalid property value”
    This occurs after – ListBox1.List(0, 9) = “Value £s”. Any ideas anyone

    Sub displaylistbox()
    Dim i1 As Integer, i2 As Integer
        ListBox1.Clear
        ListBox1.MultiSelect = fmMultiSelectSingle
        ListBox1.ColumnCount = 13
        ListBox1.ColumnWidths = “4 cm;2 cm; 2.5 cm;1 cm;2.5 cm;2.5 cm;2.5 cm;2.5 cm;2.5 cm ;2.5 cm ;2.5 cm “
        ListBox1.AddItem “Name”
        ListBox1.List(0, 1) = “Post Code”
        ListBox1.List(0, 2) = “Lead Recd.”
        ListBox1.List(0, 3) = “wk #”
        ListBox1.List(0, 4) = “Status”
        ListBox1.List(0, 5) = “Date”
        ListBox1.List(0, 6) = “Tel”
        ListBox1.List(0, 7) = “Mobile”
        ListBox1.List(0, 8) = “Lead Code”
        ListBox1.List(0, 9) = “Value £s”
        ‘ListBox1.List(0, 10) = “Points”
        For i2 = 1 To my_dbase(0, 0)
            If my_dbase(i2, 0) = 1 Then
                i1 = ListBox1.ListCount – 1
                ListBox1.AddItem my_dbase(i2, 1)
                ListBox1.List(i1, 1) = my_dbase(i2, 5)
                ListBox1.List(i1, 2) = Format(my_dbase(i2, 9), “ddd dd-mmm-yyyy”)
                ListBox1.List(i1, 3) = my_dbase(i2, 10)
                ListBox1.List(i1, 4) = my_dbase(i2, 12)
                ListBox1.List(i1, 5) = Format(my_dbase(i2, 11), “ddd dd-mmm-yyyy”)
                ListBox1.List(i1, 6) = my_dbase(i2, 6)
                ListBox1.List(i1, 7) = my_dbase(i2, 7)
                ListBox1.List(i1, 8) = my_dbase(i2, 8)
                ListBox1.List(i1, 9) = my_dbase(i2, 13)
                ‘ListBox1.List(i1, 10) = my_dbase(i2, 14)
            End If
        Next i2
    End Sub

  28. There is a limit on the number of columns you can have in a listbox. When I hit that limit, I end up combining two fields into one or putting two listboxes next to each other. It’s a pain.

  29. Thanks Dick,

    I figured that must be the case but couldn’t find any limitation anywhere and somewhere had seen a reference to ‘unlimited’ columns in a listbox.

    In practice there’s usually some reasonably easy ‘way round’.

  30. Thanks for this,

    just a comment

    When you have

    Dim Rng As Range

    don’t forget before end sub

    Set Rng = Nothing

  31. Hi
    Thanks for sharing your wealth of knowledge in Excel VBA, I have benefited from one of your codes shown above.. I was wondering if you could help me slightly further, I have managed to adjust your code (shown below) to work for me, but I am unable to get this codes to populate a multi column listbox with cells values obtain from excel range (i.e. A2:C10).. but the problem is i have rows that are empty (i.e A5:C5, row 5) and I want the listbox values to exclude this row. is it possible to amend the sode below to do this please…

    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
            With Me.ListBox1
                .AddItem cell.Value
                .List(.ListCount – 1, 1) = cell.Offset(0, 1).Value
                .List(.ListCount – 1, 2) = cell.Offset(0, 2).Value
            End With
        Next cell
       
    End Sub

  32. Hi
    I have small problem
    1. I have combobox and few labels, which read in sheet rows and columns
    I need read information read in combobox and label to one row which element like

  33. Hi
    I have small problem
    1. I have combobox and few labels, which read in sheet rows and columns
    I need read information read in combobox and label to one row which element like
    read information from difrent row
    2. If I have more comboboxes i using one of them and read information from difrent rows when i change the element i combobox then every other combobox and labels change row to this same
    Thank you for help
    Patryk

  34. Respected sir ,

    i want to combo box in tow combo box with if condition with example

    if you share example with codding , plz send me , it great help for me

  35. Hi,
    I was developing a listbox with filtered range. My requirement is mentioned herein below:
    1. The listbox will populate with 4 columns and with filtered range.
    2. 4 columns are Column A, Column B, Column D and Column V in the workbook’s sheet3.
    3. Filter will be done before populating the listbox.
    4. Filter criteria is name of label.

    Please suggest a code.
    Thanks in advance.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.