Need the Userform Combobox to display value from list only once

[SHUTTEHFACE]

Good morning,


My current code is:

Private Sub UserForm_Initialize()


'fill PS's Supervisor Name


For Each Value In [SupervisorList]
Me.cmbSupervisor.AddItem Value
Next Value


'fill PS's Shift and Level


For Each Value In [ShiftLevelList]
Me.cmbShiftLevel.AddItem Value
Next Value


End Sub

The lists I have have repeat values and I need the combobox to display these only once.


Thanks!


M

 

[Weaver]

You need the scripting dictionary, great for collecting items. Check this site for CreateObject("scripting.dictionary") and you should find an answer, possibly even one I submitted.

 

[SHUTTEHFACE]

Thank you for the help Weaver! I've found a few references from the search but I'm not sure at where to nest it. How do I use the following with the lists I currently have? I think I will try to use:

Sub noduplix()
Dim e, z As Object
With Cells(1).Resize(Cells(Rows.Count, 1).End(xlUp).Row, 1)
Set z = CreateObject("[COLOR=#417394]Scripting[/COLOR].Dictionary")
For Each e In .Value: z.Item(e) = Empty: Next
.ClearCells(1).Resize(z.Count, 1) = Application.Transpose(z.keys)
.Sort .Cells(1), 1
End With
End Sub

 

[SHUTTEHFACE]

OK Here is the latest version of what I have but I keep getting "Subscript out of Range Error"

Private Sub UserForm_Initialize()

Dim v, e
With Sheets("PR Schedule").SupervisorList
    v = .Value
End With
With CreateObject("scripting.dictionary")
    .comparemode = 1
    For Each e In v
        If Not .exists(e) Then .Add e, Nothing
    Next
    If .Count Then Me.cmbSupervisor.List = Application.Transpose(.keys)
End With
End Sub

 

[SHUTTEHFACE]

Got it working with this:

Private Sub CommandButton2_Click()


Sheets("PS Scheduler").Range("PSList").Cells(cmbPS.ListIndex + 1, 1).EntireRow.Delete


Dim MyUniqueList As Variant, i As Long
    With Me.cmbPS
        .Clear ' clear the combobox content
        MyUniqueList = UniqueItemList(Range("PSList"), True)
        For i = 1 To UBound(MyUniqueList)
            .AddItem MyUniqueList(i)
        Next i
        .ListIndex = 0 ' select the first item
    End With


End Sub




Private Sub UserForm_Initialize()


Dim MyUniqueList As Variant, i As Long
    With Me.cmbPS
        .Clear ' clear the combobox content
        MyUniqueList = UniqueItemList(Range("PSList"), True)
        For i = 1 To UBound(MyUniqueList)
            .AddItem MyUniqueList(i)
        Next i
        .ListIndex = 0 ' select the first item
    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(UniqueItemList)
        End If
    End If
    On Error GoTo 0
End Function




Private Sub CommandButton1_Click()


Unload UserForm2


End Sub