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
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