adding unique items to multiple comboboxes

dizzydunham

New Member
Joined
Mar 22, 2014
Messages
30
I have managed to populate a combobox on userform from a column in a table with unique items (to remove duplicates.)
I want to do the same with several other comboboxes with items from other columns in the same table.
What do I need to do to the code to make this work. as duplicating some of the below doesn't work!?
Code:
Sub RAISE_VAF_Click()

    'The Excel workbook and worksheets that contain the data, as well as the range placed on that data
    Dim wbBook As Workbook
    Dim wsSheet As Worksheet
    Dim rnData As Range
    Dim vaData As Variant               'the list, stored in a variant
    Dim ncData As New VBA.Collection    'the list, stored in a collection
    Dim lnCount As Long                 'the count used in the On Error Resume Next loop.
    Dim vaItem As Variant               'a variant representing the type of items in ncData
    'Instantiate the Excel objects.
    Set wbBook = ThisWorkbook
    Set wsSheet = wbBook.Worksheets("VAF Tracker")
    'Using Sheet1,retrieve the range of the list in Column 12 company
    With wsSheet
        Set rnData = .ListObjects("Table1").ListColumns(12).DataBodyRange
        
    End With
    'Place the list values into vaData.
    vaData = rnData.Value
    'Place the list values from vaData into the VBA.Collection.
    On Error Resume Next
        For lnCount = 1 To UBound(vaData)
        ncData.Add vaData(lnCount, 1), CStr(vaData(lnCount, 1))
    Next lnCount
    On Error GoTo 0
    'Clear the combo box (in case you ran the macro before),
    'and then add each unique variant item from ncData to the combo box.
    With UserForm2.CBXCompany.Object
        .Clear
        For Each vaItem In ncData
            .AddItem ncData(vaItem)
        Next vaItem
    End With

Thanks for any help and direction received.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
How about this
Code:
    Dim Cl As Range
    
    With CreateObject("scripting.dictionary")
        For Each Cl In Sheets("VAF Tracker").ListObjects("Table1").ListColumns(12).DataBodyRange
            If Not .exists(Cl.Value) Then .Add Cl.Value, Nothing
        Next Cl
         ComboBox1.Clear
         ComboBox1.List = Application.Transpose(.keys)
         .RemoveAll
    End With
    
    With CreateObject("scripting.dictionary")
        For Each Cl In Sheets("VAF Tracker").ListObjects("Table1").ListColumns(2).DataBodyRange
            If Not .exists(Cl.Value) Then .Add Cl.Value, Nothing
        Next Cl
         ComboBox2.Clear
         ComboBox2.List = Application.Transpose(.keys)
         .RemoveAll
    End With
This will populate combobox1 with unique values from table1 col 12 and combobox2 with uniques from col 2
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,847
Members
452,361
Latest member
d3ad3y3

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
Back
Top