I am using the code below to select a city from a drop down list. The list
is generated from a range on a worksheet. If i run the userform by itself
when i type a letter it selects the first word in the list that starts with
that letter and so on. However when i show this userform, by method of:
Userform1.show, the "auto complete" doesnt work. Any one know of any reason
this would happen? Do i need to load the userform in a different way?

Private Sub UserForm_Initialize()
Dim MyUniqueList As Variant, i As Long
With Me.ComboBox1
.Clear
.AddItem ""
MyUniqueList = UniqueItemList(Range("ProjectCode!G1:G1000"), True)
For i = 1 To UBound(MyUniqueList)
.AddItem MyUniqueList(i)
Next i
.ListIndex = 0
.AutoWordSelect = True
End With
End Sub
Private Function UniqueItemList(InputRange As Range, HorizontalList As
Boolean) As Variant
Dim cl As Range, cUnique As New Collection, i As Long, uList() As Variant
Application.Volatile
On Error Resume Next
For Each cl In InputRange
If cl.Formula < "" Then
cUnique.Add cl.value, CStr(cl.value)
End If
Next cl
UniqueItemList = ""
If cUnique.Count 0 Then
ReDim uList(1 To cUnique.Count)
For i = 1 To cUnique.Count
uList(i) = cUnique(i)
Next i
UniqueItemList = uList
If Not HorizontalList Then
UniqueItemList = Application.WorksheetFunction.Transpose(UniqueItem List)
End If
End If
On Error GoTo 0
End Function

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200707/1