lifeson
09-14-2007, 12:34 AM
Morning folks
its Friday :cloud9:
How do I amend this code which works well for a single column combobox to poulate a two column listbox
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
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 :think:
I suppose I should ask is there an alternative way of achieving the desired result
its Friday :cloud9:
How do I amend this code which works well for a single column combobox to poulate a two column listbox
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
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 :think:
I suppose I should ask is there an alternative way of achieving the desired result