'Place all code in the Userform
Option Explicit 
 
Dim FArray() 
Dim DataList As Range 
Dim MyList As String 
 
Private Sub UserForm_Initialize() 
    Dim Found As Long, i As Long 
    Dim cel As Range 
     
     'Set Range Name to suit
    MyList = "Data" 
     
    Set DataList = Range(MyList) 
    ReDim FArray(DataList.Cells.Count) 
    i = -1 
     
    For Each cel In DataList 
        On Error Resume Next 
        Found = Application.WorksheetFunction.Match(CStr(cel), FArray, 0) 
        If Found > 0 Then GoTo Exists 
        i = i + 1 
        FArray(i) = cel 
Exists: 
        Found = 0 
    Next 
    ReDim Preserve FArray(i) 
    Call BubbleSort(FArray) 
    ComboBox1.ListRows = i + 1 
    ComboBox1.List() = FArray 
End Sub 
 
Private Sub ComboBox1_AfterUpdate() 
    Dim MyAdd As String 
    Dim Found As Long 
     
    On Error Resume Next 
    Found = Application.WorksheetFunction.Match(ComboBox1, FArray, 0) 
    If Found > 0 Then 
        DoEvents 
    Else 
        DataList.End(xlDown).Offset(1) = ComboBox1 
        Set DataList = Union(DataList, DataList.End(xlDown)) 
        MyAdd = "=" & ActiveSheet.Name & "!" & DataList.Address 
        ActiveWorkbook.Names.Add Name:=MyList, _ 
        RefersTo:=MyAdd 
    End If 
End Sub 
 
Private Sub CommandButton1_Click() 
    Cells(Cells.Rows.Count, 4).End(xlUp).Offset(1) = ComboBox1 
    Set DataList = Nothing 
    Unload UserForm1 
End Sub 
 
Sub BubbleSort(MyArray As Variant) 
     
    Dim First           As Integer 
    Dim Last            As Integer 
    Dim i               As Integer 
    Dim j               As Integer 
    Dim Temp            As String 
    Dim List            As String 
     
    First = LBound(MyArray) 
    Last = UBound(MyArray) 
    For i = First To Last - 1 
        For j = i + 1 To Last 
            If MyArray(i) > MyArray(j) Then 
                Temp = MyArray(j) 
                MyArray(j) = MyArray(i) 
                MyArray(i) = Temp 
            End If 
        Next j 
    Next i 
End Sub