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.
Me.ListBox1.RowSource = "Sheet1!DaysList"
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:
Dim cell As Range
Dim Rng As Range
Set Rng = .Range("a2", .Range("a2").End(xlDown))
For Each cell In Rng.Cells
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
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.