Solved: Multi Column Listbox - Need 2nd Col Item

[Ken Puls]

Hi guys,

I'm trying to populate a couple of text boxes on a userform from a multi column listbox. I can't seem to figure out how to break out the value from the second column...

[vba]With Me
.tbSupplierName = .lbSuppliers.Value
End With[/vba]

... will return the first column value. But how to get the second?

[mvidas]

Hey Ken,

Can't you do[vba].Column(2).Value 'may be a 1 if zero-based[/vba]? Or set the boundcolumn to 2?

Matt

[Ken Puls]

Hmmm...

Answered myself, although I don't think this is ideal. It also makes a recursive call, despite my attempt to avoid it:

[vba]Private Sub lbSuppliers_Click()
lEvents = lEvents + 1

If lEvents <= 1 Then
With Me
.tbSupplierName = .lbSuppliers.Value
.lbSuppliers.BoundColumn = 2
.tbSupplierJonas = .lbSuppliers.Value
End With
End If
lEvents = lEvents - 1
End Sub[/vba]

I'll mark it solved, but if anyone has a better suggestion, I'm game for it.

Thanks!

[Ken Puls]

LOL!

Thanks, Matt! Posted at same time.

It looks like it is 0 based, but I can't get it to set with .columns(1).value. Give me a runtime error '424' of Object Required on that line. Strange...

[mvidas]

How did [vba].lbSuppliers.Column(2).Value 'may be a 1 if zero-based[/vba]work for you?

[mvidas]

Did you forget to add the lbSuppliers in there like I did?

[Ken Puls]

Did you forget to add the lbSuppliers in there like I did?

Nope.

[mvidas]

I haven't dealt with a multicolumn listbox in a while, let me play around a little more, see what I can figure out
Too bad we can't use VB's listview!

[mvidas]

Ken -
Easy enough! Set the TextColumn to 2, and the BoundColumn to 1
.Value returns the first column
.Text returns the second

Matt

[Ken Puls]

Very Nice, Matt!

[Bob Phillips]

Am I missing something, why not just use?

[vba].lbSuppliers.List(.lbSuppliers.ListIndex, 1)[/vba]

[Ken Puls]

Am I missing something...

Uh, no...

<-- Missing something