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:
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):
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
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.
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:
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.
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.
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? :-)
lol….. -_-
hi
And if you just want to show column A and C in a listbox
Is it possible?
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
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
great, thank you
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
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
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.
I prefer this method for a 1-column combobox/listbox; fast, simple and flexible
Private Sub UserForm_Initialize()sq = Sheets(1).UsedRange.Columns(4)
ComboBox1.List = sq
End Sub[VB]
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:
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:
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
@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.
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
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.
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.
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:
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.