Getting multi column data into a Listbox from a 2D Array

PJ0302917
PJ0302917 used Ask the Experts™
on
Hi

I'm struggling to get some data from a 2D array into a listbox. I've included a code snippet. At the moment I'm only getting the first column populated.

The code below opens a second Workbook
The 2D array is called filterManagerWB and 5 elements are populated for each sheet.
The idea is to get 5 columns in the listbox with the number of rows dependant on number of sheets.

        Set filterManagerWB = Workbooks.Open(filenamePath)
        SheetCount = filterManagerWB.Sheets.Count
        ReDim filterInfo(1 To SheetCount, 1 To 5)
        
        For i = 1 To SheetCount
            filterInfo(i, 1) = filterManagerWB.Sheets(i).Name
            filterInfo(i, 2) = filterManagerWB.Sheets(i).Cells(1, 2).Value   'Contains Created By
            filterInfo(i, 3) = filterManagerWB.Sheets(i).Cells(2, 2).Text      'Contains Created Date
            filterInfo(i, 4) = filterManagerWB.Sheets(i).Cells(3, 2).Value   'Contains Updated By
            filterInfo(i, 5) = filterManagerWB.Sheets(i).Cells(4, 2).Text      'Contains Updated Date
        Next i
        
        lstFilterList.List = sheetNames

Open in new window


I'm stuck how to populate the listbox properly
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
NorieAnalyst Assistant

Commented:
Which array are trying to you populate the listbox with?

Is it sheetNames, which I assume is declared and populated elsewhere, or filterInfo which you are populating in the posted code?

Author

Commented:
Its Filter Info
So the data looks something like this

SheetName1 |Created By Data|Created By Date |Updated By|Updated By Date
SheetName2 |Created By Data|Created By Date |Updated By|Updated By Date
SheetName3 |Created By Data|Created By Date |Updated By|Updated By Date
SheetName4 |Created By Data|Created By Date |Updated By|Updated By Date
SheetName5 |Created By Data|Created By Date |Updated By|Updated By Date

The Created / Updated data is taken from the first few cells on each worksheet
NorieAnalyst Assistant

Commented:
Do either of these work?
 lstFilterList.List = filterInfo

' or

 lstFilterList.List = Application.Transpose(filterInfo)

Open in new window

Author

Commented:
Thanks for the help, I've just tried both

lstFilterList.List = filterInfo gives me a single column (in the listbox) of SheetNames. E.g

SheetName1 |
SheetName2 |
SheetName3 |
SheetName4 |
SheetName5 |

lstFilterList.List = Application.Transpose(filterInfo) gives me all the data for one element in the array (but still in one column of the listBox)

SheetName1         |
Created By Data.  |
Created By Date.  |
Updated By.          |
Updated By Date |
Analyst Assistant
Commented:
This might be a stupid question, but what's the ColumnCount property of the listbox set to?

It should be set to the no of columns of data you have, which I think should be 5 in this case.

Author

Commented:
Thats it, code was right originally, just didn't know about that property on the Listbox.

Thanks a lot for helping me

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial