Dynamically populating a 14-column listbox

[theSquirrel]

I am currently working on a project that requires me to dynamically add
items to a listbox.

HAHA, if it were just that easy!

The listbox I am trying to populate is a 14 column monster named
"lstMain". I am pulling information from an Excel database.

I am having problems while sending adding the items in a loop. I can
add a line individually but once I introduce the loop it fails with a
type mismatch.

Here is the code and issue details.

The program progression is as follows:
- UI initializes
- UI Activates, where it dynamically populates a listbox called
"lstSeriesName"
- User selects an item from lstSeriesName to display items in lstMain
- get an error before displaying anything.

The failing code:
'@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#
Private Sub lstSeriesName_Change()
'when clicked, list the relevent cards

'If SelectingAll = True Then Exit Sub
'If ClearingAll = True Then Exit Sub

'clear the card list to make way for the new list
Me.lstMain.Clear

'create a loop to loop through all the cells in the list
Dim StartRow As Long
Dim curCell As Range
Dim RowIndex As Long

StartRow = 20

CardsShowing = 0

For RowIndex = StartRow To LastRow
Set curCell = Worksheets(DBName).Cells(RowIndex, 1)
If Me.lstSeriesName.Value = "All" Then
If FilterFunction(DBName, RowIndex) = True Then
Me.lstMain.AddItem RowArray(RowIndex)
CardsShowing = CardsShowing + 1
End If
ElseIf curCell.Value = Me.lstSeriesName.Value And
FilterFunction(DBName, RowIndex) = True Then
CardsShowing = CardsShowing + 1
Me.lstMain.AddItem RowArray(RowIndex)
End If
Next RowIndex

Me.lblCardsShowing.Caption = CardsShowing
Me.lblTotalCards.Caption = TotalCards

End Sub
'@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#
Notes about above code:
1.) LastRow is a form level variable that holds the rowindex of the
lastrow of data.
2.) DBName is a form level variable that holds the name of the database
we are pulling information from
3.) FilterFunction is a UDF that functions properly in another part of
the project. (i can post that if its relevent)



Supporting Code:
'@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#
Public Function RowArray(RowIndex As Long) As Variant

Dim MyArray(13) As Variant

With Worksheets("CardDatabase")
MyArray(0) = .Cells(RowIndex, 2).Value
MyArray(1) = .Cells(RowIndex, 3).Value
MyArray(2) = .Cells(RowIndex, 4).Value
MyArray(3) = .Cells(RowIndex, 5).Value
MyArray(4) = .Cells(RowIndex, 6).Value
MyArray(5) = .Cells(RowIndex, 10).Value
MyArray(6) = .Cells(RowIndex, 12).Value
MyArray(7) = .Cells(RowIndex, 13).Value
MyArray(8) = .Cells(RowIndex, 14).Value
MyArray(9) = .Cells(RowIndex, 15).Value
MyArray(10) = .Cells(RowIndex, 16).Value
MyArray(11) = .Cells(RowIndex, 17).Value
MyArray(12) = .Cells(RowIndex, 18).Value
MyArray(13) = .Cells(RowIndex, 19).Value
End With

RowArray = MyArray
Erase MyArray

End Function
'@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#

The problem comes from the following line of code from teh first
section of code:
Me.lstMain.AddItem RowArray(RowIndex)

With this line I get a Type Mismatch error.

I have also tried the following variations:
Me.lstMain.Column RowArray(RowIndex)
Me.lstMain.List RowArray(RowIndex)

and got an Invalid use of property error.

I am at an end of my ideas on how to get this to function correctly.

Any help would be great, also if any additional code is needed, please
ask and I will post it.

Thanks in advance,
theSquirrel

 

[Nigel]

Without studying your code in great detail. Is the fact rowindex runs from
20 to lastrow, whilst your array will start from base 0 something to do with
the errors your are getting?

 

[Tom Ogilvy]

You appear to think you can AddItem an array and it will fill all columns.
This is incorrect.

You have to Additem a single value. Then you can fill in the remaining
columns using

for i = 1 to 13
.list(.Listcount,i) = myarray(i)
Next

(as an example - obviously your code is not structured to access myarray )

however, the maximum number of columns you can have for an unbound control
is 10.

From help on the Listcount property:

Setting ColumnCount to 0 displays zero columns, and setting it to -1
displays all the available columns. For an unbound data source, there is a
10-column limit (0 to 9).

A bound data source would be using rowsource.



So you might need to build your data source in another range or on another
sheet, then redefine the rowsource.



--

Regards,

Tom Ogilvy

 

[theSquirrel]

Thanks Tom,

I was unaware of the 10column limit on unbound controls. It looks like
I will have to review the design and make the appropriate changes.

Thanks!