Two column combobox

[Rick Trenidad]

Hi, I’m Rick Trenidad. I have a question. I have a sheet sheet1 that has in column B a unique number and in Column G a text! The text in Colunm G can appear many times. It’s the last name of a person. So this person can have many cases going! My question is is it possible to create a combobox showing the unique number and the lastname? The reason I’m asking is that I do have a combobox with lastname but when selection is made only the last lastname selected is shown in the userform. I hope you can help me with this problem! Thanks Rickshaws

 

[mikerickson]

Something like this will fill the ComboBox.

Private Sub UserForm_Initialize()
    Dim oneCell as Range

    With ComboBox1
        .TextColumn = 1
        .BoundColumn = 2
        For each oneCell in Range(Cells(1, 2), Cells(Rows.Count,2).End(xlUp))
            .AddItem oneCell.Value
            .List(.ListCount - 1, 1) = oneCell.EntireRow.Range("G1").Value
        Next oneCel
    End With
End Sub

And the .BoundColumn will determine which column's value is returned by the .Value property. The .TextColumn property will determine what value is shown in the box and which is returned by the .Text property.

With ComboBox1
    MsgBox .Text & " from column B was selected, because .TextColumn= 1"
    MsgBox .Value & " from column G was selected, since .BoundColumn = 2"
End With

 

[Rick Trenidad]

Good morning mrExcel MVP, the combobox is working! But I actually want it now to fill the userform! I’ll send a picture of the form! The labels are in Dutch. Sir I hope you can help me with this part

 

[mikerickson]

I don't read Dutch, but I'm guessing that you want all those other controls to be filled with data once the user makes a selection from the (two columned) Zoeken OP# combobox.

I'm also guessing that that data is stored on a worksheet. The first thing to do would be to find what row the selected record is on.
I would amend my code above to include the address of the source row as a hidden third column of the combobox.

Private Sub UserForm_Initialize()
    Dim oneCell as Range

    With ComboBox1
        .ColumnCount = 3
        .ColumnWidths = ";;0"
        .TextColumn = 1
        .BoundColumn = 2
        For each oneCell in Range(Cells(1, 2), Cells(Rows.Count,2).End(xlUp))
            .AddItem oneCell.Value
            .List(.ListCount - 1, 1) = oneCell.EntireRow.Range("G1").Value
            .List(.ListCount - 1, 2) = oneCell.EntireRow.Address(,,,True)
        Next oneCell
    End With
End Sub

That would be used by the ComboBox change event to fill the other controls.

Private Sub ComboBox1_Change()
    Dim rngRowSelected As Range

    With ComboBox1
        If .ListIndex <> -1 Then
            Set rngRowSelected = Range(.List(.ListIndex, 2)).EntireRow
        End If
    End With

    If rngRowSelected Is Nothing Then
        TextBox1.Text = vbNullString
        TextBox2.Text = vbNullString
        'etc.
    Else
        With rngRowSelected
            TextBox1.Text = .Range("A1").Text
            TextBox2.Text = .Range("C1").Text
            'etc.
        End With
    End If
End Sub

The correspondence between column and control (TextBox1 is column A values, TextBox2 is column C,...) would need to be adjusted to match your situation.

 

[Rick Trenidad]

Many thanks mrExcel MVP it worked perfectly! And the language wasn’t a problem for you! Actually I’m from the beautiful island of Curacao a Dutch Caribbean island!
I’m also very glad to have registered to the webblink of MrExcelMVP.
Again thanks! I wouldn’t have struggled alot to get this done and you did it in minutes! Unbelievable! Thank you very much!
Rick Trenidad

 

[Rick Trenidad]

Dear MREXCEL MVP
I’m having a problem with the two columns dropdown. When I select the number and the name the search is made and correct fills all the textboxes etc. When the command UPDATE button is chosen all the fields are ok and the fields that are edited as well. But the number field in column B is changed in the name of column G. How can I avoid this?