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.
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?
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.
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
Hi,
This doesn’t seem to work with a ComboBox…?
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.
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
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
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
Hmm, I see that’s what I said a few months ago in comment 5.
Jon,
Very smart and elegant – just what I like :)
Cheers
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
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
Thanks – fixed my issue! Genius!
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.
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.
These code fragments are very helpful.
thank you
@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
I 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?
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.
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
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
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
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
Thanks guys! Worked great!
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!
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
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!
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
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
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.
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’.
Thanks for this,
just a comment
When you have
don’t forget before end sub
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…
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
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
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
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
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.