Microsoft Office Tutorials and References
In Depth Information
ComboBoxes
comboBoxes
A ComboBox combines the features of a ListBox and a TextBox, in that you can select an item from
its drop-down list, or you can type an item into the ComboBox that is not included in its list. Most
of the time, you’ll use the ComboBox the same way you’d use Data Validation, where a drop-down
arrow is visible for revealing the list of items that are available for selection.
If you want to limit the ComboBox to only accept items from the drop-down
list, set its Style property to 2 - fmStyleDropDownList .
ComboBoxes allow only one item to be selected; you cannot select multiple items in a ComboBox the
way you can with a ListBox. However, ComboBoxes are populated much the same way as ListBoxes,
with a RowSource property and an AddItem method.
Suppose you want to guide the users of your workbook to select a year that
is within three years — past or future — of the current year. The following
code could accomplish that, with Figure 19-6 showing the ComboBox’s list
after the drop-down arrow was clicked, assuming the current year is 2011:
Private Sub UserForm_Initialize()
With ComboBox1
.Clear
Dim iYear As Integer, jYear As Integer
jYear = Format(Date, “YYYY”)
For iYear = 1 To 7
ComboBox1.AddItem jYear - 3
jYear = jYear + 1
Next iYear
End With
End Sub
figurE 19-6
As with a ListBox, if the items needed to populate the ComboBox are listed on a worksheet, it does
not mean you must refer to them with the RowSource property. You can leave the RowSource
property empty, and populate the ComboBox (same concept applies to a ListBox) with the following
code example, assuming the values are listed in range A1:A8 with no blank cells in that range:
Private Sub UserForm_Initialize()
ComboBox1.List = Range(“A1:A8”).Value
End Sub
If you want the first item in the drop-down list to be automatically visible in
your ComboBox, you can add the following line before the End Sub line,
assuming the ComboBox is named ComboBox1:
ComboBox1.ListIndex = 0
 
Search JabSto ::




Custom Search