Solved: Populate listbox

[lifeson]

Morning folks
its Friday
How do I amend this code which works well for a single column combobox to poulate a two column listbox
[vba]Private Sub Rowsource(ByVal idx As String, ByRef combo As msforms.ComboBox)
Dim iLastRow As Long
Dim i As Long
Dim iItem As Long
Dim aryItems
Sheets("Price").Select
iLastRow = Cells(Rows.Count, "A").End(xlUp).row
ReDim aryItems(1 To iLastRow)
For i = 2 To iLastRow
If Cells(i, "A").Value = idx Then
iItem = iItem + 1
aryItems(iItem) = Cells(i, "D").Value
End If
Next i
ReDim Preserve aryItems(1 To iItem)
combo.Clear
combo.List = aryItems
'use the following line to call and populate
'replacing idx with key i.e. sPrem
'Call Rowsource(idx, Me.name of combo box)
End Sub
[/vba]

I need it to work that when it is called the idx value is known and comes from column A
The list box should show the values from a sheet that correspond to idx from columns C & D

I suppose I should ask is there an alternative way of achieving the desired result

[lifeson]

So chuffed, i think I have done it myself


[vba]Private Sub UserForm_Initialize()
Dim c As String 'column to use
Dim idx As String 'lookup key
Dim ilastrow As Long
Dim aryItems

idx = "PART2500" 'this will be determined seperately
c = "D" 'column that contains the price
ilastrow = Cells(Rows.Count, "A").End(xlUp).Row

'which sheet to use
Sheets("Price").Select
With ListBox1
ReDim aryItems(1 To ilastrow)
For i = 2 To ilastrow
If Cells(i, "A").Value = idx Then
.AddItem Cells(i, "C").Value
.List(.ListCount - 1, 1) = Cells(i, c)
End If
Next i
End With

End Sub
[/vba]

It works but is it right?

[Bob Phillips]

Using an array as you originally did,

[vba]

Private Sub Rowsource(ByVal idx As String, ByRef combo As msforms.ComboBox)
Dim iLastRow As Long
Dim i As Long
Dim iItem As Long
Dim aryItems
Sheets("Price").Select
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
ReDim aryItems(1 To iLastRow, 1 To 3)
For i = 2 To iLastRow
If Cells(i, "A").Value = idx Then
iItem = iItem + 1
aryItems(1, iItem) = Cells(i, "D").Value
aryItems(2, iItem) = Cells(i, "E").Value
aryItems(3, iItem) = Cells(i, "F").Value
End If
Next i
ReDim Preserve aryItems(1 To 3, 1 To iItem)
combo.Clear
combo.List = Application.Transpose(aryItems)
'use the following line to call and populate
'replacing idx with key i.e. sPrem
'Call Rowsource(idx, Me.name of combo box)
End Sub
[/vba]