Reusing VBA Codes using Arguments

[animas]

I am trying to use the below code to create unique and sorted list dropdown inside a combobox list. What I am looking for is a subruoutine with arguments (List Range, Combobox), so that I can reuse the code for multiple comboboxes. How to modify the below code for this. Codes between "Private Sub UserForm_Initialize() .. End Sub" will be reused.

Thanks.

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 = "[B]Table_Customer[Customer Name]"[/B]
 
    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)
    [B]cmbName[/B].ListRows = i + 1
    [B]cmbName[/B].List() = FArray
End Sub

 

[RoryA]

Something like:

Private Sub SortList(DataList As Range, cmbName As MSForms.ComboBox)
    Dim Found As Long, i As Long
    Dim cel As Range
 
    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)
    cmbName.ListRows = i + 1
    cmbName.List() = FArray
End Sub

 

[animas]

Private Sub UserForm_Initialize()
    SortList(Table_Customer[Customer Name], cmbCustomerName)...
End Sub

is showing error. My range is a column of a table. how can I pass this as argument?

 

[RoryA]

Remove the parentheses - they don't belong there - and add the Range call:

Private Sub UserForm_Initialize()
    SortList Range("Table_Customer[Customer Name]"), cmbCustomerName 
End Sub

 

[animas]

Wonderful. I tried that with multiple comboboxes and working nice.
Many thanks.