Microsoft Office Tutorials and References
In Depth Information
ListBoxes
There are two common methods by which a ListBox is populated with items. In the preceding
example, the 12 months of the year could be listed on a worksheet, say on Sheet2 in range A1:A12.
To have the ListBox display the list of months, you can enter Sheet2!A1:A12 as the RowSource
property for that ListBox.
In many cases, however, you’ll want to populate your ListBox without having to store the items
on a worksheet. The UserForm’s Initialize event is perfect for populating your ListBox with a
dynamic or static list of items. Suppose you want to list the names of various countries. The
following code does that using the AddItem method in the UserForm’s Initialize event, which you can
easily append when you want to add or omit a country name.
Private Sub UserForm_Initialize()
With ListBox1
.AddItem “England”
.AddItem “Spain”
.AddItem “France”
.AddItem “Japan”
.AddItem “Australia”
.AddItem “United States”
End With
End Sub
When you programmatically populate a ListBox (or, as you’ll see, a ComboBox),
be sure to clear the control’s RowSource property or you will get a runtime error
when you call (initialize) the UserForm.
The following code lists all the visible worksheets in your workbook, and excludes the worksheets
that are hidden:
Private Sub UserForm_Initialize()
With ListBox1
.Clear
Dim wks As Worksheet
For Each wks In Worksheets
If wks.Visible = xlSheetVisible Then .AddItem wks.Name
Next wks
End With
End Sub
ListBoxes support many events, and using the Click event, for example, this code activates the
worksheet whose name you click, with the ListBox’s MultiSelect property set to
0 - fmMultiSelectSingle :
Private Sub ListBox1_Click()
Worksheets(ListBox1.Value).Activate
End Sub
Search JabSto ::




Custom Search