Creating a List of Unique Values for a Combobox Rowsource

[Ark68]

Hello,

I am using the code below to take the values in column G (starting at G6) of worksheet "LISTS1" to create the rowsource for combobox "cb_league1" in my userform.

    With Worksheets("LISTS1")
        Set Rng = .Range("G6", .Range("G6").End(xlDown))
    End With
    For Each cell In Rng.Cells
        With Me.cb_league1
            .AddItem cell.Value
            .List(.ListCount - 1, 1) = cell.Offset(0, 1).Value
            .List(.ListCount - 1, 2) = cell.Offset(0, 2).Value
        End With
    Next cell

It works, but I would like to enhance it (if possible) to use only unique values in the rowsource. With the code I get now, I am getting multiple instances of the same value, which is unnecessary.

 

[Ark68]

Hey David ... yeah ... error testing always seems to to be most work to figure out for me and I have been playing around in between our posts to come up with very awkward ways of doing this. Some work, some don't, others just lead to more questions ... perhaps there is a more advanced and efficient way of doing things beyond my scope.

Here's the scenario of how a user will use this userform.

The database (DATA) hold a listing of all our current usergroups (League) and their different programs (programs). When a group wishes to use a field, they are issued a permit. This userform prepares the permit based on the information that has is in the database. So, when say a WMGSA Novice Rep softball team wishes to have a permit, the permit issuer (the one using the userform with limited knowledge of the needs of the usergroup) user the form to drill down to find the default setup information already on hand. lb_sport1 = Softball, lb_calibre 2 = REP, cb_league1 = WMGSA and cb_program1 = Novice. The remainder of the permit fields relating to setup for this group are already known (from referencing that row in the database) and the fields of the permit are applied automatically. The permit issuer does not need to know anything except the customer information on record to get the setup details.

Now consider a new REP softball league wishing a permit, one that is not in the database. They will not have any default data to populate the permit. Lets consider a new group "TCWSA". Permit issuer lb_sport1 = softball, lb_calibre1 = REP. Now, the way we have it set up currently, is the league will populate only with "WMGSA", which we don't want. At this point we want the permit issuer to enter the new TCWSA into the cb_league1. Now, of course the filter criteria for this will fail as their is now data. The permit issuer will now be required to manually enter cb_program1 as there is nothing (hopefully) to select from. There is no record for this new submission, so the permit fields will not auto populate and will have to be manually entered, unlike when a record exists.

Please don't spend a lot of time on this David, I will more than understand with thanks for knowledge and direction you've already given. This is more a learning process for me than anything practical at this point, and I've been trying different techniques to accomplish a final product. The guidance and suggestions are more than enough, the actual code is a bonus! As I said, I think I can accomplish what I need in a very inefficient and primitive way.