Solved: Fill combo box with unique value

[av8tordude]

I like to fill cboIdent combo box with unique values base on the selection made in ACLIST combo box. Can someone assist thanks

[Tommy]

I copied some stuff from the help files and modified it.


Private Sub ACLIST_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim cmbLst As String, firstAddress As Variant, c As Range, Alst() As String
Dim I As Long
With Range("ad9", Range("ad" & Rows.Count).End(xlUp))
Set c = .Find(ACLIST.Text, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
If InStr(1, cmdlst, c.Offset(, -1).Value) = 0 Then
cmdlst = cmdlst & "," & c.Offset(, -1).Value
End If
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
If Left(cmdlst, 1) = "," Then cmdlst = Right(cmdlst, Len(cmdlst) - 1)
Alst = Split(cmdlst, ",")
For I = LBound(Alst) To UBound(Alst)
UserForm1.cboIDENT.AddItem Alst(I)
Next
End Sub

[av8tordude]

If I select an item from ACLIST it display as expect, but if I select another item from ACLIST, is still shows the previous list. Any way of not display the previous list if I select a different item from ACLIST?

[Tommy]

Sure is :)


Private Sub ACLIST_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim cmbLst As String, firstAddress As Variant, c As Range, Alst() As String
Dim I As Long
UserForm1.cboIDENT.Clear
With Range("ad9", Range("ad" & Rows.Count).End(xlUp))
Set c = .Find(ACLIST.Text, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
If InStr(1, cmdlst, c.Offset(, -1).Value) = 0 Then
cmdlst = cmdlst & "," & c.Offset(, -1).Value
End If
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
If Left(cmdlst, 1) = "," Then cmdlst = Right(cmdlst, Len(cmdlst) - 1)
Alst = Split(cmdlst, ",")
For I = LBound(Alst) To UBound(Alst)
UserForm1.cboIDENT.AddItem Alst(I)
Next
End Sub

[av8tordude]

Thank you Tommy. :-)