Link to home
Start Free TrialLog in
Avatar of PJ0302917
PJ0302917

asked on

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

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
Avatar of Norie
Norie
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?
Avatar of PJ0302917
PJ0302917

ASKER

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
Avatar of Norie
Norie
Do either of these work?
 lstFilterList.List = filterInfo

' or

 lstFilterList.List = Application.Transpose(filterInfo)

Open in new window

Avatar of PJ0302917
PJ0302917

ASKER

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 |
ASKER CERTIFIED SOLUTION
Avatar of Norie
Norie
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of PJ0302917
PJ0302917

ASKER

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

Thanks a lot for helping me