Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamically populating a 14-column listbox
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamically populating a 14-column listbox
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? -- Cheers Nigel "theSquirrel" wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamically populating a 14-column listbox
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" wrote in message oups.com... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamically populating a 14-column listbox
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! Tom Ogilvy wrote: 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" wrote in message oups.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamically populating textboxes on a form | Excel Programming | |||
Populating a multi column listbox with ADO Recordset | Excel Programming | |||
Populating Column Headers in a ListBox | Excel Programming | |||
populating a multi-column Listbox | Excel Programming | |||
populating a multi-column Listbox | Excel Programming |