Microsoft Office Tutorials and References
In Depth Information
ListBox Techniques
As you might expect, you can set the RowSource property via code rather than with the
Properties window. For example, the following procedure sets the RowSource property for a
ListBox before displaying the UserForm. In this case, the items consist of the cell entries in a
range named Categories on the Budget worksheet.
UserForm1.ListBox1.RowSource = “Budget!Categories”
If the ListBox items aren’t contained in a worksheet range, you can write VBA code to fill the
ListBox before the dialog box appears. The following procedure fills the ListBox with the names
of the months by using the AddItem method.
Sub ShowUserForm2()
‘ Fill the list box
With UserForm1.ListBox1
.AddItem “January”
.AddItem “February”
.AddItem “March”
.AddItem “April”
.AddItem “May”
.AddItem “June”
.AddItem “July”
.AddItem “August”
.AddItem “September”
.AddItem “October”
.AddItem “November”
.AddItem “December”
End With
End Sub
In the preceding code, notice that I set the RowSource property to an empty string.
This setting is to avoid a potential error that occurs if the Properties window has a
nonempty RowSource setting. If you try to add items to a ListBox that has a non-null
RowSource setting, you’ll get a “permission denied” error.
You can also use the AddItem method to retrieve ListBox items from a range. Here’s an example
that fills a ListBox with the contents of A1:A12 on Sheet1 .
For Row = 1 To 12
UserForm1.ListBox1.AddItem Sheets(“Sheet1”).Cells(Row, 1)
Next Row
Search JabSto ::

Custom Search