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.test.xlsm

[paulked]

Hi and welcome to the forum.

test.xlsm

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.

test.xlsm

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!test.xlsm

[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 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.

[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