Microsoft Office Tutorials and References
In Depth Information
Populating ListBoxes and ComboBoxes with Unique Items
In Figure 20-5, column A contains a list of clothing items that were sold in a department store. A
unique list of these items was compiled in a ComboBox as shown in Figure 20-5, with the following
code to demonstrate how to populate the ComboBox in this manner when the length of the source
list is not known, and some cells in the source list might have no entry.
Private Sub UserForm_Initialize()
‘Declare variables for a Collection and cell range.
Dim myCollection As Collection, cell As Range
‘Error bypass to set a new collection.
On Error Resume Next
Set myCollection = New Collection
‘Open a With structure for the ComboBox
‘Clear the ComboBox
‘Open a For Next loop to examine every cell starting with A2
‘and down to the last used cell in column A.
For Each cell In Range(“A2:A” & Cells(Rows.Count, 1).End(xlUp).Row)
‘If the cell is not blank...
If Len(cell) <> 0 Then
‘Clear the possible error for a Collection
‘possibly not having been established yet.
‘Add the cell’s value to the Collection.
myCollection.Add cell.Value, cell.Value
‘If there is no error, that is, if the value does not