Archive of Mr Excel Message Board

Back to Controls in Excel archive index
Back to archive home

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?

Re: Combo Boxes HELP!

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()
ComboBox1.Clear
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

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.