Fill a multicolumn listbox with data from a range in another works

D

Dale Fye

I have some data on a worksheet(TaskStandards2) that I want to display in a
multi-column listbox on a user form. I want to call a subroutine to fill the
listbox and have written the following code. What I want it to do is look in
the first column of the "TaskStandards2" worksheet for the first occurance of
the TaskValue (in this instance: "1.1.1"), and then load into the listbox,
the values in columns 2-5 for that and each successive row in the table that
match the PassedValue value.

I orignally declared lb as a listbox in the subroutine, but when I called it
from my routine using:

Call FillList(me.lst_Standards, "1.1.1")

I got an type mismatch error, with this line of code highlighted. When I
moved the mouse over me.lst_Standards it indicated the value was null. I
don't want to pass the value, I want to pass a reference to the control, so I
changed the declaration in the subroutine to 'lb as Control'; that seemed to
work.

Now, when I now call the subroutine, it errors out on the line that reads:

lb.List(intRowPointer - intMatchRow + 1, 1) = sht.Cells(intRowPointer, 2)

with an error # 381 (Could not set the list property. Invalid property
array index)
with values(intRowPointer = 2, intMatchRow = 2).

My guess is that it has something to do with the lb.AddItem statement, but I
don't know what.

Would appreciate some help here. Thanks

-----------
Public Sub FillList(lb As Control, TaskValue As String)

Dim lookupStandards As Range
Dim intMatchRow As Integer, intRowPointer As Integer
Dim sht As Worksheet

Set lookupStandards = Worksheets("TaskStandards2").Range("A:A")

'Clear the list box
lb.Clear

On Error Resume Next
intMatchRow = WorksheetFunction.Match(TaskValue, lookupStandards, 0)
If Err.Number <> 0 Then
MsgBox "No matching Criteria"
Exit Sub
End If
On Error ErrorHandler

intRowPointer = intMatchRow
Set sht = ActiveWorkbook.Worksheets("TaskStandards2")
With sht
Do While sht.Cells(intRowPointer, 1) = TaskValue
lb.AddItem
lb.List(intRowPointer - intMatchRow + 1, 1) =
sht.Cells(intRowPointer, 2)
lb.List(intRowPointer - intMatchRow + 1, 2) =
sht.Cells(intRowPointer, 3)
lb.List(intRowPointer - intMatchRow + 1, 3) =
sht.Cells(intRowPointer, 4)
lb.List(intRowPointer - intMatchRow + 1, 4) =
sht.Cells(intRowPointer, 5)
intRowPointer = intRowPointer + 1
Loop
End With
Set sht = Nothing
Exit Sub
ErrorHandler:

End Sub
 
D

Dale Fye

Disregard,

I was adding 1 to the rowindex counter, and it should have been zero based.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top