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!
Userform Combobox RowSource - Eliminate Duplicate Selections
-
- Lounger
- Posts: 35
- Joined: 17 Feb 2010, 22:08
-
- Administrator
- Posts: 78596
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Userform Combobox RowSource - Eliminate Duplicate Selections
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:
Modify the name of the worksheet and of the combo box as needed.
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
Best wishes,
Hans
Hans
-
- Lounger
- Posts: 35
- Joined: 17 Feb 2010, 22:08
Re: Userform Combobox RowSource - Eliminate Duplicate Selections
Thanks Hans!
I made a slight modification to the line:
Instead I used:
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?
I made a slight modification to the line:
Code: Select all
Set wsh = Worksheets("Sheet1")
Code: Select all
Set wsh = ActiveSheet
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?
-
- Administrator
- Posts: 78596
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Userform Combobox RowSource - Eliminate Duplicate Selections
You're very welcome to modify the code to suit your purposes!Cardstang wrote:I made a slight modification to the line: <snip>
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
Best wishes,
Hans
Hans
-
- Lounger
- Posts: 35
- Joined: 17 Feb 2010, 22:08
Re: Userform Combobox RowSource - Eliminate Duplicate Selections
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.
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.
-
- Administrator
- Posts: 78596
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Userform Combobox RowSource - Eliminate Duplicate Selections
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.
If you do neither, the dropdown list will be empty.
Best wishes,
Hans
Hans