Adding items in a Listbox with multiple columns

Question

How can I add items in a list with 2 columns? It adds the items just in the first column if I use ListBox.AddItem. I want to add items in the 2nd column too. Thanks!

score 24 · Accepted Answer · answered Aug 07 '11 at 14:32

24

By using the List property.

ListBox1.AddItem "foo"
ListBox1.List(ListBox1.ListCount - 1, 1) = "bar"

answered Aug 07 '11 at 14:32

GSerg

76,472
17
159
346

Thanks a lot. Now if I want to get that value how can I do it? I mean to get the value in the first column I do ListBox1.Value ... bot for the 2nd column? – Andrei Ion Aug 07 '11 at 14:45
1

@Andrei Ion In the same way. `List` is a property, works both ways. – GSerg Aug 07 '11 at 14:46
Yes, I guessed that it has to be the same way but I won't know the row of the selected item... I know that the column is 1 but the row? I guess that ListBox1.ListCount-1 is the row when you add the value... how do I know what's the row when a Value is selected? Thanks a lot! – Andrei Ion Aug 07 '11 at 14:48
1

@Andrei Ion The selected index is `.ListIndex`. Please use the object browser instead of wanderng in the dark. Press F2. Find the property and press F1. – GSerg Aug 07 '11 at 14:51
Thanks. I try to use the help and it helps me a lot but sometimes I really don't know what to search and then I use stackoverflow. Anyway... thanks a lot... it really helped me! – Andrei Ion Aug 07 '11 at 14:53
You find *complementary* explanations and how to overcome the `.AddItem` method's built-in limitation of 10 columns via *array* assignment at [Populate ListBox with multiple columns](https://stackoverflow.com/questions/47528558/vba-excel-populate-listbox-with-multiple-columns/47531440#47531440) – T.M. Sep 09 '20 at 13:28

score 3 · Answer 2 · answered Dec 29 '16 at 14:42

There is one more way to achieve it:-

Private Sub UserForm_Initialize()
Dim list As Object
Set list = UserForm1.Controls.Add("Forms.ListBox.1", "hello", True)
With list
    .Top = 30
    .Left = 30
    .Width = 200
    .Height = 340
    .ColumnHeads = True
    .ColumnCount = 2
    .ColumnWidths = "100;100"
    .MultiSelect = fmMultiSelectExtended
    .RowSource = "Sheet1!C4:D25"
End With End Sub

Here, I am using the range C4:D25 as source of data for the columns. It will result in both the columns populated with values.

The properties are self explanatory. You can explore other options by drawing ListBox in UserForm and using "Properties Window (F4)" to play with the option values.

Not exactly the answer, but a more efficient method to add a multicolumn list. — FCastro, Apr 03 '17 at 20:57

score -6 · Answer 3 · answered Aug 28 '14 at 15:12

-6

select propety

Row Source Type => Value List

Code :

ListbName.ColumnCount=2

ListbName.AddItem "value column1;value column2"

answered Aug 28 '14 at 15:12

KONG

39
1

1

This does not add any value to column 2. The `AddItem` method takes a value and optional index (row index) as argument. – David Zemens Aug 28 '14 at 15:20
@DavidZemens since the rowSource type is "Value list" delimited strings are interpreted same as columns. above code from Kong will work. – Krish Aug 28 '14 at 16:13
@krishKM in which version of Excel? Using 2010, `Debug.Print ListBox1.RowSourceType` raises a "member not found" error. I can see it in the Locals window, but not in the Properties pane. – David Zemens Aug 28 '14 at 16:51
1

@krish there is no RowSourceType property on Listboxes (or Comboboxes) in Excel. You are probably thinking of Access. – Caltor Feb 24 '15 at 10:34

Adding items in a Listbox with multiple columns

3 Answers3

Linked

Related