Referencing values from columns in listbox

[dlee83]

Hi,

Thanks for any help in advance.

I'm trying to get the data from a selected line in a listbox. I can reference the initial column with the following, "Me.ComboBox1.Value = ListBox1.Value" - but how do I extrapolate the other columns data to place in the relevent textboxes?

So, combobox1 gets the first line, then textboxes 1,2,3 & 4 need the other 4 columns data from selected listbox data?

Me.ComboBox1.Value = ListBox1.Value
Me.TextBox1.Value = ListBox1 COLUMN2
Me.TextBox2.Value =ListBox1 COLUMN3
Me.TextBox3.Value = ListBox1 COLUMN4
Me.TextBox4.Value =ListBox1 COLUMN5

 

[Micron]

If the listbox is on a user form then
userform.ListBox2.column(0)
Column collection is zero based so 0 is the first column. If no item is selected then that won't work. IIRC, in Access you can get at the column values when nothing is selected by using the list index - don't know if that is possible in Excel. I'd have to try it if you're trying to get values without selecting anything.

If the listbox is on a sheet then the syntax will be different (I don't seem to have any sample code for that yet).

 

[Skyybot]

Try this.

Dim rng As Range, str As String
str = Sheet1.ListBox1.ListFillRange
Set rng = Sheet1.Range(str)
Me.ComboBox1.Value = ListBox1.Value
Me.TextBox1.Value = rng(1, 2).Value
Me.TextBox2.Value = rng(1, 3).Value
Me.TextBox3.Value = rng(1, 4).Value
Me.TextBox4.Value = rng(1, 5).Value

 

[dlee83]

Thanks Micron,

Yes, that works

Me.ComboBox1.Value = ListBox1.Value
Me.TextBox1.Value = ListBox1.Column(1)
Me.TextBox2.Value = ListBox1.Column(2)
Me.TextBox3.Value = ListBox1.Column(3)
Me.TextBox4.Value = ListBox1.Column(4)

So simple

Thank you

 

[Micron]

Glad I could help. Please mark your thread as solved if you have a solution so that others don't open and read to the end just to find out they didn't need to.