Microsoft Office Tutorials and References
In Depth Information
Populating ListBoxes and ComboBoxes with Unique Items
‘already exist in the Collection, add the item to the ComboBox.
If Err.Number = 0 Then .AddItem cell.Value
‘Loop to the next cell.
‘Close the With structure for the ComboBox.
If you want the first item in the ComboBox’s list to be visible when the UserForm
is called, add this line before the End Sub line:
ComboBox1.ListIndex = 0
To expand a bit on the possible usefulness of listing unique items in a ComboBox, see the example
in Figure 20-6, where two Label controls were added (named Label2 and Label3) to the right of
the ComboBox. When the ComboBox value is changed with the following code, Label2’s caption
reflects the value item, and Label3’s caption sums the items sold in column B for the item that was
selected in the ComboBox.
Private Sub ComboBox1_Change()
Label2.Caption = _
“Total “ & ComboBox1.Value & “ Sold:”
Label3.Caption = _
WorksheetFunction.SumIf(Columns(1), ComboBox1.Value, Columns(2))