Combobox Population

[stck2mlon]

I got this from a book and it is almost what I need but I need to twea
it and can't seem to make it work.

The For i& makes me pick a range from 3 to 300 or a different number
Is there a way to make it pickup only unique names in that column, s
that the combo box only populates with those names and not a bunch o
blank spaces???

Private Sub UserForm_Initialize()
Dim i&
With ThisWorkbook.Sheets("Active Collection")
For i& = 3 To 300
cmbClient.AddItem .Cells(i&, 4).Value
Next i&
End With
cmbClient.ListIndex = 0
End Sub

I would like to still start with row 3

 

[Bob Phillips]

You need to get a unique range before loading the combobox.

Take a look at Data>Filter>Advanced Filter with the copy to another
location, un ique records.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Cecilkumara Fernando]

stck2mlon,
Here is an answer given to a similar post by Carl
I am posting it as it is. with adjustments to suit your environment you can
get it work.
It worked for me.
HTH,
Cecil

Here:
subSetupform()
For Each c In frmAddEntry.Controls
If Left(c.Name, 2) = "cb" Then
c.List = CreateList(YourRangeAddress)
End If
Next c
End Sub


Function CreateList(myRange)
Dim myControl
Dim mystring As String
Dim Cell As Range
Dim NoDupes As New Collection
Dim i As Integer
Dim j As Integer
Dim Swap1, Swap2, Item
Dim cbList() As Variant

' The next statement ignores the error caused by attempting to add
' a duplicate key to the collection.
' The duplicate is not added - which is just what we want!
On Error Resume Next
For Each Cell In Range(myRange)
If Cell.Value <> "" Then NoDupes.Add Cell.Value, Cell.Value
' Note: the 2nd argument (key) for the Add method must be a string
Next Cell

On Error GoTo 0

' Sort the collection (optional)
For i = 1 To NoDupes.Count - 1
For j = i + 1 To NoDupes.Count
If NoDupes(i) > NoDupes(j) Then
Swap1 = NoDupes(i)
Swap2 = NoDupes(j)
NoDupes.Add Swap1, before:=j
NoDupes.Add Swap2, before:=i
NoDupes.Remove i + 1
NoDupes.Remove j + 1
End If
Next j
Next i

' Add the sorted, non-duplicated items to a ComboBox
ReDim cbList(NoDupes.Count) 'reset to same number as in no dupes
j = 0
For Each Item In NoDupes
j = j + 1
If Item <> "" Then cbList(j) = Item
Next Item

For j = 1 To NoDupes.Count ' Remove names from the collection.
NoDupes.Remove 1 ' removes the current first member on each
iteration.
Next

CreateList = cbList()
End Function

Now go buy a Power Programming book by :John Walkenbach
BEST investment you'll ever make!

Carl

 

[billyb]

Try this-

Private Sub UserForm_Initialize()
Dim i&, x&, Unique As Boolean
With Sheets("Active Collection")
For i = 3 To 300
If Cells(i, 4).Value <> "" And i > 3 Then
Unique = True
For x = 4 To i
If .Cells(x - 1, 4) = .Cells(i, 4).Value Then
Unique = False
Exit For
End If
Next x
If Unique Then
cmbClient.AddItem .Cells(i, 4).Value
End If
End If
Next i
End With
cmbClient.ListIndex = 0
End Sub

Regards,
billyb

 

[stck2mlon]

Thanks everyone...these both worked great