Multiple column Combobox

[JM]

Is it posible for a combobox to return the value of two or more
columns?

I have a simple User form having a combobox with 3 or more columns that
takes the data from sheet1 column A, B and C for example. When I run
the form and open the combobox , it display all the 3 columns but the
problem is when I select one record only the value of the first column
is returned.

UserForm1.ComboBox1.ColumnHeads = True
UserForm1.ComboBox1.ColumnWidths = "50,50,50"
UserForm1.ComboBox1.RowSource = "A2:C255"
UserForm1.ComboBox1.ShowDropButtonWhen = fmShowDropButtonWhenAlways

Is there a way for the combobox return the value of column A & column C
for example (in a string)?
Because the problem I have is that in column A I have duplicated
records and I need column C for isolating the record I want. With only
the first column if I do a VLOOKUP after I only stop on the first
occurrence and there is no way to find the second occurrence

Thanks
JM

 

[Bob Phillips]

Here is an example

Dim i As Long
Dim j As Long
Dim iRow As Long
iRow = 10
With Me.ComboBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
For j = 1 To .ColumnCount
ActiveSheet.Cells(iRow, j).Value = .List(i, j - 1)
Next
iRow = iRow + 1
End If
Next i
End With

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[JM]

Thanks Bob,

Where should I enter this code, in the initialise Sub of my userForm?

I have tried different place (in the Sub UserForm_Initialize(), in the
sub where I call the form, in the Sub ComboBox1_Change() , etc...) but
for some reason id doesn't wrk and I receive an error.
Could you give more more details?
Thanks
JM

 

[Bob Phillips]

I am afraid I have no idea. It certainly should not be in the Initialize
event, but it should be wherever you want to read the combobox value, such
as within a button click event.

The code I gave allowed for multiple selections, so you probably just need

Dim j As Long
Dim iRow As Long
iRow = 10
With Me.ComboBox1
For j = 1 To .ColumnCount
ActiveSheet.Cells(iRow, j).Value = .List(.ListIndex, j - 1)
Next
End With


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)