Userform Combobox RowSource - Eliminate Duplicate Selections

[Cardstang]

Hi all,

I have a Combobox in a Userform that I'm using to trigger additional code based on the selection. I can get it to work, but my rowsource is a large range and contains many duplicates. There are actually only about 25 or so different possible selections, but the way the Combobox displays them there are many, many duplicates as well as some blank entries. My rowsource is E2:E500. Sometimes column E has 10 entries and other times it has 400.

I could set up a rowsource to list only the 25 or so selections, but not every selection is valid every time. Sometimes there are just 5. I'd like the Combobox to reflect only the currently available selections.

I've also set up a Pivot Table on another sheet to be the RowSource, but that seems a bit clunky.

Is there another or better way to accomplish this?

Thanks!

[HansV]

In a situation like this, it's best to leave the RowSource of the combo box blank, and to fill the list using code in the Userform_Initialize event of the userform:

Code: Select all

Private Sub UserForm_Initialize()
  Dim wsh As Worksheet
  Dim r As Long
  Dim m As Long
  Dim col As New Collection
  Set wsh = Worksheets("Sheet1")
  m = wsh.Cells(wsh.Rows.Count, 5).End(xlUp).Row
  On Error Resume Next
  For r = 2 To m
    col.Add Item:=wsh.Cells(r, 5).Value, Key:=CStr(wsh.Cells(r, 5).Value)
  Next r
  For r = 1 To col.Count
    Me.ComboBox1.AddItem col(r)
  Next r
End Sub

Modify the name of the worksheet and of the combo box as needed.

[Cardstang]

Thanks Hans!

I made a slight modification to the line:

Code: Select all

 Set wsh = Worksheets("Sheet1")

Instead I used:

Code: Select all

Set wsh = ActiveSheet

That way I could use this on any sheet.




Another related question...

Is it possible to have listed in the combobox one set of selections, and then when a selection is made have it actually use a different value that is tied to the selection?

For example, the combobox would list a selection of colors (blue, red, pink, yellow, green), and then when the selection is made, the value that is used is the ColorIndex value (5, 3, 7, 6, 4).


Does that make sense?

[HansV]

I made a slight modification to the line: <snip>

You're very welcome to modify the code to suit your purposes!

If the combo box had a fixed range as row source, you could have used a multi-column combo box with a hidden column that contains the value to be returned. But in your situation, it's better to use a Select Case statement:

Code: Select all

Private Sub ComboBox1_Change()
  Dim lngColorIndex As Long
  Select Case Me.ComboBox1
    Case "Blue"
      lngColorIndex = 5
    Case "Red"
      lngColorIndex = 3
    Case "Pink"
      lngColorIndex = 7
    Case "Yellow"
      lngColorIndex = 6
    Case "Green"
      lngColorIndex = 4
    Case Else
      ' No item
      lngColorIndex = 1
  End Select
  ' Do something with lngColorIndex here, e.g.
  Selection.Interior.ColorIndex = lngColorIndex
End Sub

ComboBox1 should be replaced with the name of your combo box, and you can have the code perform any other action, of course.

[Cardstang]

I've inserted that code for ComboBox2 and maybe I'm not doing it right.

The color selections do not show in the pulldown in the Combobox. However, I can type in the word "Green" or "Red" or any of the other colors listed and it will use that color. So I guess it's partially working. I don't receive any errors.

[HansV]

You must either set the RowSource of ComboBox2 to a range that contains color names, or use code to populate the list in the UserForm_Initialize event.
If you do neither, the dropdown list will be empty.