MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Combo Boxes HELP!

Posted by Ken on November 13, 2001 2:22 PM

Within the "properties" of the combo box is a line titled -listfillrange- where one can input a specific range of cells for the combo box to reference. My question is how do I get the combo box to "NOT" list duplicate information that exists in a column. For ex: If in cells a1:10 the name Joe is listed three times, how do I get it to show up only once in the list box?

Posted by Juan Pablo on November 13, 2001 4:47 PM

You have to use Advanced Filter to get unique values into another range, or you could use a loop that adds the values (If it is an ActiveX ComboBox) when the userform is initialized for example...

Private Sub UserForm_Initialize()
For Each cll In Range("A1:A10")
If Application.CountIf(Range("A1", cll.Address), cll) = 1 Then
ComboBox1.AddItem cll.Value
End If
Next cll
End Sub

Juan Pablo