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](http://msdn.microsoft.com/en-us/library/office/gg251814.aspx), plenty of examples, post back when you have issue to troubleshoot. — PatricK, Sep 19 '13 at 00:21
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](http://meta.stackexchange.com/questions/156810/stack-overflow-question-checklist) — Siddharth Rout, Sep 19 '13 at 05:32
possible duplicate of [Adding items in a Listbox with multiple columns](http://stackoverflow.com/questions/6973287/adding-items-in-a-listbox-with-multiple-columns) — GSerg, Feb 11 '15 at 11:10
Another link to pharma scam (vba4all [dot] com)... please delete your comment, Meehow/@vba4all and replace with Wayback Machine saved copy. — Jason R. Mick, May 11 '16 at 23:55

score 1 · Answer 1 · answered May 09 '16 at 14:51

1

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

answered May 09 '16 at 14:51

Lbissy

11
1

Welcome to Stack Overflow! While this code may answer the question, it would be better to include some _context_, explaining _how_ it works and _when_ to use it. Code-only answers are not useful in the long run. – Benjamin W. May 09 '16 at 15:08

score -1 · Answer 2 · edited Jan 11 '14 at 15:18

-1

listBox1.Items.AddRange(strArray)

edited Jan 11 '14 at 15:18

kleopatra

51,061
28
99
211

answered Jan 11 '14 at 15:01

ABH

1

This answer concerns VB.NET and shoud be deleted. – GSerg Feb 11 '15 at 11:09

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

2 Answers2