Custom RowSource for Combo Boxes

[ilya2004]

Hi All
Please see the attached example:

I have some list boxes that I am trying to fill with ranges from a spreadsheet.

I am building a form to help users browse employee data. I would like to be able to narrow the data down by country.

In the attached example, I would like the form to give the user the options of the countries that are in column 1. This should populate the data from column 2 with only the name of the employees that are in those countries.

The problem is that it has to be dynamic and the data can come in different orders, so I need it to be dynamic to read off the list.

Any ideas?

 

[CharlesH]

HI,

See if the following helps.
1. First you need to create an "ActiveX" command button in Sheet1 if thats the sheet you have the data. I used sheet1 you will need to change the "Sheet1" to the Sheet name you are using.

Paste the following code to sheet code module

Private Sub CommandButton1_Click()
Dim lrow As Long
Dim Mycel As Range
For Each Mycel In Sheets("Sheet1").Range("A2:A" & Range("A65536").End(xlUp).Row)
         UserForm1.ComboBox1.AddItem (Mycel)
Next
UserForm1.Show
End Sub

Now paste the following code to to the Userform code module.

Private Sub ComboBox1_Change()
Dim lrow As Long
Dim Mycel As Range
For Each Mycel In Sheets("Sheet1").Range("A2:A" & Range("A65536").End(xlUp).Row)
         If Cells(Mycel.Row, 1).Text = UserForm1.ComboBox1.Value Then
            UserForm1.ComboBox2.AddItem Cells(Mycel.Row, 2).Text
         End If
        
    Next
End Sub

 

[ilya2004]

This is an excellent start!

I am have two difficulties.
The first box is repeating the country names. If Germany is on the list twice, it is listing it twice in the combo box.
Also, for some reason it is only filtering by country for the first selection I make. I.e. when I select "Germany" the first time, it only gives me "German" employees, but once I switch to "France" it populates the list with all of the names, not just "French".
I tried to upload the file to a google doc to share, but I am not sure if it worked. Here is the link if it did:

https://docs.google.com/leaf?id=0Bx1zPaEVJ3WuMDE1NGVkMjktNTcwYi00N2Y4LTgwZWMtNGM0NTU3OTFmMDM3&hl=en

 

[CharlesH]

Ok,

Sorry for the problem in the following code I added a line for each code.
The new lines should clear the combobox before the new entry.

Private Sub ComboBox1_Change()
Dim lrow As Long
Dim Mycel As Range
UserForm1.ComboBox2.Clear ''added code here
For Each Mycel In Sheets("Sheet1").Range("A2:A" & Range("A65536").End(xlUp).Row)
         If Cells(Mycel.Row, 1).Text = UserForm1.ComboBox1.Value Then
            UserForm1.ComboBox2.AddItem Cells(Mycel.Row, 2).Text
         End If
        
    Next
End Sub

Private Sub CommandButton1_Click()
Dim lrow As Long
Dim Mycel As Range
UserForm1.ComboBox1.Clear ''added code here
For Each Mycel In Sheets("Sheet1").Range("A2:A" & Range("A65536").End(xlUp).Row)
         UserForm1.ComboBox1.AddItem (Mycel)
Next
UserForm1.Show
End Sub

 

[ilya2004]

Great!

That fixed the problem with the employee name. Now only the correct employees are showing up. Do you have any ideas how to fix the first issue with the countries. Right now when I am clicking on the combo box, I still see:

<table border="0" cellpadding="0" cellspacing="0" width="137"><col style="width: 103pt;" width="137"> <tbody><tr style="height: 14.25pt;" height="19"> <td style="height: 14.25pt; width: 103pt;" width="137" height="19">United States</td> </tr> <tr style="height: 14.25pt;" height="19"> <td style="height: 14.25pt;" height="19">Germany</td> </tr> <tr style="height: 14.25pt;" height="19"> <td style="height: 14.25pt;" height="19">France</td> </tr> <tr style="height: 14.25pt;" height="19"> <td style="height: 14.25pt;" height="19">United States</td> </tr> <tr style="height: 14.25pt;" height="19"> <td style="height: 14.25pt;" height="19">France</td> </tr> <tr style="height: 14.25pt;" height="19"> <td style="height: 14.25pt;" height="19">Germany</td> </tr> <tr style="height: 14.25pt;" height="19"> <td style="height: 14.25pt;" height="19">United States</td> </tr> <tr style="height: 14.25pt;" height="19"> <td style="height: 14.25pt;" height="19">Germany</td> </tr> <tr style="height: 14.25pt;" height="19"> <td style="height: 14.25pt;" height="19">France</td> </tr> <tr style="height: 14.25pt;" height="19"> <td style="height: 14.25pt;" height="19">United States</td> </tr> </tbody></table>
Instead of just each country once.

 

[CharlesH]

HI,

When you select the "Country" the name of the country will be displayed in the combobox. Now a question. Why would you want to show only the Country selected when you are looking for the "Employee" for that country. If the Country combo box will only show the selected Country how would you then select another Country?

 

[ilya2004]

I see what you're saying. Sorry I wasn't clear.

What I want is for the first box to act as a filter for the values in the second box. That way, when I open the form, I am given a choice of 3 countries. Once I pick the country that I am operating under, I then get a limited list of only those employees that work in that country.

(In real application, this will then trigger pulling other data about this employee, but that part I don't need help with).

 

[CharlesH]

HI,

Place this code in the sheet code module.

Private Sub CommandButton1_Click()
Dim Mycel
Dim a, z
a = Range("A2", Range("A" & Rows.Count).End(xlUp))
With CreateObject("scripting.dictionary")
    .comparemode = vbTextCompare
    For Each Mycel In a
        If Not IsEmpty(Mycel) Then
            If Not .exists(Mycel) Then .Add Mycel, Nothing
        End If
    Next
    z = .keys
End With
With UserForm1.ComboBox1
    .Clear
    .List = Application.Transpose(z)
End With
UserForm1.Show
End Sub

 

[ilya2004]

Fantastic! It's always so much cleaner when someone knows that they are doing. (I don't even want to tell you what mine looked like).

Thanks very much for your help!

 

[CharlesH]

Hi,

Thanks for letting me know.