Multiple Rowsource in combo box?

[lordbodom]

Hello. I am really new to VBA and have a question. I am creating a combobox where I can be able to view and select the correct person from a list. My excel is broken down in 3 columns: first name, last name and zip code. I know how to make one column a source but running into issues if someone has the same name but different zip. Is there an easy way to have the combobox show not just data from one column but all 3? For eg.

bob smith 12345

Attaching an example file to show. Thanks in advance.25453

[paulked]

Hi and welcome to the forum.

25454

Remove the RowSource property and fill the combo with this:



Private Sub UserForm_Activate()
Dim i As Long
ComboBox1.Clear
For i = 6 To Cells(Rows.Count, 1).End(xlUp).Row
ComboBox1.AddItem Cells(i, 1) & " " & Cells(i, 2) & " " & Cells(i, 3)
Next
End Sub

[lordbodom]

Hi and welcome to the forum.

25454

Remove the RowSource property and fill the combo with this:



Private Sub UserForm_Activate()
Dim i As Long
ComboBox1.Clear
For i = 6 To Cells(Rows.Count, 1).End(xlUp).Row
ComboBox1.AddItem Cells(i, 1) & " " & Cells(i, 2) & " " & Cells(i, 3)
Next
End Sub


Thank you for the quick reply. I was hoping I would be able to use your code and tweak it to how my layout actually is but I failed :(. If its not too much bother could you please look at this updated sheet? I am looking to have the columns first name, last name and zip populate. Thank you again for the help!25455

[paulked]

Change the column numbers:


Private Sub UserForm_Activate()
Dim i As Long
ComboBox1.Clear
For i = 6 To Cells(Rows.Count, 1).End(xlUp).Row
ComboBox1.AddItem Cells(i, 3) & " " & Cells(i, 4) & " " & Cells(i, 7)
Next
End Sub

[大灰狼1976]

I don't have excel installed at home. So I can't open the attachment or test it.
But I read the code of of friend paulked. I think I can write it like below.



Private Sub UserForm_Activate()
Dim r&,arr
ComboBox1.Clear
r=Cells(Rows.Count, 1).End(xlUp).Row
arr=evaluate(replace("c6:cx&"" ""&d6:dx&"" ""&g6:gx","x",r))
ComboBox1.list=arr
End Sub

[paulked]

That works Okami :thumb and as you are using .List instead of .AddItem to fill the CB there is no need to clear it.

@LordyLordy if you use Okami's code you can delete the line ComboBox1.Clear

[大灰狼1976]

@paulked
Yes! You're right!
Thank you for your advice, and have a nice day.:beerchug:

[Artik]

Data concatenated from many cells may look nice, but may not be practical when the code continues to work, after choosing in ComboBox. Therefore, I will show you another solution that has its advantages, but also has disadvantages.
Full code in UserForm module:
Option Explicit



Private Sub ComboBox1_Click()

Dim lIdx As Long


With Me.ComboBox1
lIdx = .ListIndex
If lIdx > -1 Then
MsgBox "Your choice:" & vbLf & _
.List(lIdx, 2) & " " & .List(lIdx, 3) & " " & .List(lIdx, 6) & vbLf & _
"who is a " & IIf(.List(lIdx, 1) = "M", "man", "woman") & _
" from state - " & .List(lIdx, 4) & " in " & .List(lIdx, 5)
MsgBox "Combo returns value: " & .Value & vbLf & _
"This is the value from the Ref column, " & _
"not the number of the selected item in the list"
End If
End With
End Sub




Private Sub UserForm_Initialize()
Dim i As Long
Dim rngData As Range
Dim varrData As Variant


Set rngData = Sheet1.Range("A5").CurrentRegion


With rngData
varrData = .Offset(1).Resize(.Rows.Count - 1).Value
End With


With Me.ComboBox1
.RowSource = vbNullString
.ColumnCount = UBound(varrData, 2)
.BoundColumn = 1
.ColumnWidths = "0;0;30;40;0;0"
.List = varrData
End With


End Sub
Artik