Reusing VBA Codes using Arguments

animas

Active Member
Joined
Sep 28, 2009
Messages
396
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.

Code:
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
 
Last edited:

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,820
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Something like:
Code:
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

Active Member
Joined
Sep 28, 2009
Messages
396
Code:
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,820
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Remove the parentheses - they don't belong there - and add the Range call:
Code:
Private Sub UserForm_Initialize()
    SortList Range("Table_Customer[Customer Name]"), cmbCustomerName 
End Sub
 

animas

Active Member
Joined
Sep 28, 2009
Messages
396
Wonderful. I tried that with multiple comboboxes and working nice.
Many thanks.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,629
Messages
5,838,454
Members
430,549
Latest member
jayjay2022

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top