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