Unique Combobox in Userform

[murty473]

Hi,

I'm new in here and macros. I would like to create unique comboboxes in my userform. I don't want them to be repeated in combobox.

I have 5 columns and you can see my codes below.

I appreciate for your quick response and help. Please also let me know if you need further information that I'm using.

Private Sub UserForm_Initialize()
Dim T1 As Range
Dim T2 As Range
Dim T3 As Range
Dim T4 As Range
Dim ws As Worksheet
Set ws = Worksheets("LookupLists")
For Each T1 In ws.Range("ProcessList")
  With Me.Turkcell1
    .AddItem T1.Value
  End With
Next T1
For Each T2 In ws.Range("SubProcessList")
  With Me.Turkcell2
    .AddItem T2.Value
  End With
Next T2
For Each T3 In ws.Range("KPITypeList")
  With Me.Turkcell3
    .AddItem T3.Value
  End With
Next T3
For Each T4 In ws.Range("KPINameList")
  With Me.Turkcell4
    .AddItem T4.Value
  End With
Next T4
Me.Turkcell4.SetFocus
End Sub

 

[wigi]

This will work:

Private Sub UserForm_Initialize()
    With Worksheets("LookupLists")
        Me.Turkcell1.List = UniqueEntries(.Range("ProcessList"))
        Me.Turkcell2.List = UniqueEntries(.Range("SubProcessList"))
        Me.Turkcell3.List = UniqueEntries(.Range("KPITypeList"))
        Me.Turkcell4.List = UniqueEntries(.Range("KPINameList"))
    End With
    Me.Turkcell4.SetFocus
End Sub

Function UniqueEntries(rng As Range) As String()
    s = "#"
    For Each r In rng.Cells
        If InStr(s, "#" & r.Text & "#") = 0 Then
            s = s & r.Text & "#"
        End If
    Next
    UniqueEntries = Split(Mid(s, 2, Len(s) - 2), "#")
End Function

 

[wigi]

Or with slightly different notation:

Private Sub UserForm_Initialize()
    Me.Turkcell1.List = UniqueEntries([LookupLists!ProcessList])
    Me.Turkcell2.List = UniqueEntries([LookupLists!SubProcessList])
    Me.Turkcell3.List = UniqueEntries([LookupLists!KPITypeList])
    Me.Turkcell4.List = UniqueEntries([LookupLists!KPINameList])
    Me.Turkcell4.SetFocus
End Sub

Function UniqueEntries(rng As Range) As String()
    s = "#"
    For Each r In rng.Cells
        If InStr(s, "#" & r.Text & "#") = 0 Then
            s = s & r.Text & "#"
        End If
    Next
    UniqueEntries = Split(Mid(s, 2, Len(s) - 2), "#")
End Function

 

[murty473]

Hi Wigi,

Thanks for your reply. It works, do you have any idea about combining this processes. For example when I select an item from Process List, I just want to see the related Sub Process on the combobox.

I really appreciate for your help.

Kind Regards

Or with slightly different notation:

Private Sub UserForm_Initialize()
    Me.Turkcell1.List = UniqueEntries([LookupLists!ProcessList])
    Me.Turkcell2.List = UniqueEntries([LookupLists!SubProcessList])
    Me.Turkcell3.List = UniqueEntries([LookupLists!KPITypeList])
    Me.Turkcell4.List = UniqueEntries([LookupLists!KPINameList])
    Me.Turkcell4.SetFocus
End Sub
 
Function UniqueEntries(rng As Range) As String()
    s = "#"
    For Each r In rng.Cells
        If InStr(s, "#" & r.Text & "#") = 0 Then
            s = s & r.Text & "#"
        End If
    Next
    UniqueEntries = Split(Mid(s, 2, Len(s) - 2), "#")
End Function

 

[murty473]

I handled how to do it by searching web.

Thanks for the previous answer.