Populating Multi-Column ListBox/ComboBox
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
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.
Kevin Trasler:
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?
3 November 2004, 6:32 amgonetribal:
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.
17 December 2004, 7:05 amselvavinayagam:
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
10 January 2005, 1:21 amGraham:
Hi,
This doesn’t seem to work with a ComboBox…?
23 January 2006, 12:41 amJon Peltier:
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.
23 January 2006, 7:11 amLEO:
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 February 2006, 2:46 pmRob van Gelder:
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,
10 April 2006, 12:47 amRob
Jon Peltier:
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
10 April 2006, 5:14 amJon Peltier:
Hmm, I see that’s what I said a few months ago in comment 5.
10 April 2006, 5:15 amRob van Gelder:
Jon,
Very smart and elegant - just what I like
Cheers
10 April 2006, 5:20 amJon Peltier:
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
10 April 2006, 9:13 amRob van Gelder:
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
10 April 2006, 3:42 pmTRex2006:
Thanks - fixed my issue! Genius!
31 May 2006, 11:08 amKobie:
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.
23 October 2006, 6:24 pmJohn Walkenbach:
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.
23 October 2006, 8:53 pmEmily Wong:
These code fragments are very helpful.
14 November 2006, 6:22 pmthank you
Jep:
@Kobie
Check this out: http://www.vbexplorer.com/VBExplorer/vb_feature/june2000/june2000.asp
This will do most of the job. You will need to introduce the looping through sheets to add to the listbox.
@rest
30 November 2006, 3:00 amI am looking for three columns in a listbox, but as far as I can tell the maximum is two. Is this correct? How do I introduce more columns?
Jon Peltier:
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.
30 November 2006, 10:49 amPhilippe Petrov:
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
10 September 2007, 7:37 amPam:
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
23 September 2007, 8:48 pmBig Rick:
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
26 October 2007, 9:24 amXP Pro, XL2007
Big Rick:
I think I stumbled, and I don’t use that word lightly, on to an answer.
[code]
26 October 2007, 10:09 am‘===========
With ListBox1
.Clear
Dim c As Variant
Dim x As String ‘add this variable 10.26.07