Populate listbox in Excel

Hello all,

I would like to populate my VBA ListBox1.

I need to separate all 5 columns of data.

The data source is in Sheets("Templates") .Range("A21:E48")

How can i do this please?

Thanks for your help.
LVL 11
Wilder1626Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Wilder1626Author Commented:
Thast's where i am now:

Dim lbtarget As MSForms.ListBox
Dim rngSource As Range
   
    'Set reference to the range of data to be filled
    Set rngSource = Worksheets("DB").Range("A21:E48")
   
    'Fill the listbox
    Set lbtarget = Me.Listbox1
    With lbtarget
        'Determine number of columns
        .ColumnCount = 5
        'Set column widths
        .ColumnWidths = "120;120;120;120;120"
        'Insert the range of data supplied
        .List = rngSource.Cells.Value
    End With



Now, i just need to separate with a line between the columns
0
Rory ArchibaldCommented:
Listboxes do not have that capability, I'm afraid.
0
Wilder1626Author Commented:
Is there another option?

Like a listview?
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Rory ArchibaldCommented:
You can get gridlines with a ListView but not just column dividers - I don't know if that would work for you?
0
Wilder1626Author Commented:
yes it would work

Thanks for your help
0
Wilder1626Author Commented:
How can we do this?
0
Rory ArchibaldCommented:
Oh, sorry - I thought from your last comment you'd done it!
Code would be something like this:



Dim itm As ListItem
    Dim rngSource As Range
    Dim lngRow As Long, lngCol As Long
    
    'Set reference to the range of data to be filled
    Set rngSource = Worksheets("DB").Range("A21:E48")
    
    With Me.ListView1
        .ColumnHeaders.Clear
        .ColumnHeaders.Add 1, , rngSource.Cells(0, 1), rngSource.Columns(1).Width
        .ColumnHeaders.Add 2, , rngSource.Cells(0, 2), rngSource.Columns(2).Width
        .ColumnHeaders.Add 3, , rngSource.Cells(0, 3), rngSource.Columns(3).Width
        .ColumnHeaders.Add 4, , rngSource.Cells(0, 4), rngSource.Columns(4).Width
        .ColumnHeaders.Add 5, , rngSource.Cells(0, 5), rngSource.Columns(5).Width
        .Gridlines = True
        .HideColumnHeaders = False
        .View = lvwReport
        For lngRow = 1 To rngSource.Rows.Count
            Set itm = .ListItems.Add(lngRow, , rngSource.Cells(lngRow, 1))
            For lngCol = 2 To rngSource.Columns.Count
                itm.ListSubItems.Add , , rngSource.Cells(lngRow, lngCol)
            Next lngCol
        Next lngRow
    End With

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Wilder1626Author Commented:
I have a run time error 438.

It does not want to open my userform.
Do you know why?
0
Rory ArchibaldCommented:
No, since I don't know what code is in your userform or how you are opening it. If you press Debug when you get the error, then f8 to step through the code, which line actually causes the error? (I assume you added a ListView control called ListView1 to your form).
0
Wilder1626Author Commented:
The section that give the error is that part:

 For lngRow = 1 To rngSource.Rows.Count
            Set itm = .ListItems.Add(lngRow, , rngSource.Cells(lngRow, 1))
            For lngCol = 2 To rngSource.Columns.Count
                itm.ListSubItems.Add , , rngSource.Cells(lngRow, lngCol)
            Next lngCol
        Next lngRow
0
Wilder1626Author Commented:
Even more:
only that part:

           ' Set itm = .ListItems.Add(lngRow, , rngSource.Cells(lngRow, 1))
            'For lngCol = 2 To rngSource.Columns.Count
                'itm.ListSubItems.Add , , rngSource.Cells(lngRow, lngCol)
           ' Next lngCol
0
Rory ArchibaldCommented:
Which line specifically? I tested before posting and it ran fine for me (XL2003).
0
Rory ArchibaldCommented:
Can you post your workbook (censor any confidential data)?
0
Wilder1626Author Commented:
As soon as i remove the  apostrophe from all these lines:
 ' Set itm = .ListItems.Add(lngRow, , rngSource.Cells(lngRow, 1))
            'For lngCol = 2 To rngSource.Columns.Count
                'itm.ListSubItems.Add , , rngSource.Cells(lngRow, lngCol)
           ' Next lngCol

it creates an error and it close the excel spreadsheet
0
Wilder1626Author Commented:
How can i post it?
0
Rory ArchibaldCommented:
Click the File link at the bottom of the comment box, then upload your file.
0
Wilder1626Author Commented:
0
Rory ArchibaldCommented:
What control did you have on that form? I got an error about an unavailable object and it was then removed. When I added a Microsoft ListView control 6.0 control, the code ran fine.
0
Wilder1626Author Commented:
It' working in another excel spreadsheet but not in my real one.

Really don't understand.
0
Rory ArchibaldCommented:
I've just tested on another machine just to be sure, and had the same problem.
Have you tried removing the control and re-adding a new ListView?
0
Wilder1626Author Commented:
Thanks for your help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.