VBA: How to Create and Add Items to a multi-column ListBox?

Question

How do you create a new ListBox using VBA in excel and then add items to different columns via an array?

My application of the ListBox is to populate it with data in multiple columns and have it appear where the user last clicks. I just need help with the creating and adding to the multi-column listbox.

The only way I have been able to add a new Listbox is:

ActiveSheet.ListBoxes.Add(400, 200, 100, 100).Select
With Selection
    .name = "ListBox1"
End With

This way I created the box and set the name of it so I could reference it later using:

ActiveSheet.Shapes.Range(Array("ListBox1")).Select

From here I would try to add a two dimensional array thinking it would populate the listbox in two seperate columns:

Dim strArray(2, 1) As Variant
    strArray(0, 0) = "Value 1"
    strArray(0, 1) = "Value 2"
    strArray(1, 0) = "Value 3"
    strArray(1, 1) = "Value 4"
    strArray(2, 0) = "Value 5"
    strArray(2, 1) = "Value 6"

With Selection
    .AddItem strArray
End With

I have researched that there is a setting called '.ColumnCount' but it produces a "Run-time error '438':" when i try to use this setting:

With Selection
    .ColumnCount = 2
End With

Currently, I am trying to avoid using a UserForm when using this ListBox but if that is the 'better' way of doing this procedure let me know.

have a read from MSDN, plenty of examples, post back when you have issue to troubleshoot. — PatricK
Questions asking for code must demonstrate a minimal understanding of the problem being solved. Include attempted solutions, why they didn't work, and the expected results. See also: Stack Overflow question checklist — Siddharth Rout
possible duplicate of Adding items in a Listbox with multiple columns — GSerg
Another link to pharma scam (vba4all [dot] com)... please delete your comment, Meehow/@vba4all and replace with Wayback Machine saved copy. — Jason R. Mick

Lbissy Lbissy · Accepted Answer · 2016-05-09T14:51:18

ActiveSheet.ListBoxes.Add(400, 200, 100, 100).Select
With Selection
     .name = "ListBox1"
     .ListFillRange = "$A$2:$A$11"
End With

VBA: How to Create and Add Items to a multi-column ListBox?

2 Answers