Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill a multicolumn listbox with data from a range in another works
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 -- Email address is not valid. Please reply to newsgroup only. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill a multicolumn listbox with data from a range in another works
Disregard,
I was adding 1 to the rowindex counter, and it should have been zero based. -- Email address is not valid. Please reply to newsgroup only. "Dale Fye" wrote: 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 -- Email address is not valid. Please reply to newsgroup only. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add a filtered range to a multicolumn ListBox | Excel Programming | |||
Multicolumn listbox data display | Excel Programming | |||
MultiColumn ListBox, linked to a range - select cells | Excel Programming | |||
Multicolumn Listbox with variable range | Excel Programming | |||
Fill a listbox with data from variable range | Excel Programming |