Access VBA - adding row with multiple columns

Question

I'm trying to add a multiple column item to a ListBox in access using VBA. Can't for the life of me work out how to do it though. There seems to be multiple ways on the internet, none of them have worked for me.

Here's an example:

Sub AddMultipleColumn()
'Add multiple Columns to a listbox
ListBox1.Clear 'Make sure the Listbox is empty
ListBox1.ColumnCount = 3 'Set the column Amount
'Fill the Listbox
ListBox1.AddItem "Row Number 1" 'Additem creates a new row
ListBox1.List(0, 1) = "Column Number 2" 'List(x,y) X is the row number, Y the column number
ListBox1.List(0, 2) = "Column Number 3"
ListBox1.List(0, 3) = "Column Number 4"
End Sub

http://visiblevisual.com/jupgrade/index.php/general-net/77-multiple-columns

Here's my code (already configured to have three columns):

lst_TemplateEditorList.AddItem "1"
lst_TemplateEditorList.List(0, 0) = "1"
lst_TemplateEditorList.List(0, 1) = "2"
lst_TemplateEditorList.List(0, 2) = "3"

But I get the compile error "Method or data member not found" on "lst_TemplateEditorList.List" because there's no List method/property.

I found something that used "Column" instead of "List", but that didn't work, and a few have no argument with "AddItem" which causes an error too:

lst_TemplateEditorList.AddItem
lst_TemplateEditorList.Column(0, 0) = "1"
lst_TemplateEditorList.Column(0, 1) = "2"
lst_TemplateEditorList.Column(0, 2) = "3"

But most seem to suggest the first should work, eg:

Adding items in a Listbox with multiple columns

vba listbox multicolumn add

It seems like it should be so simple... but I'm stumped. Can anyone tell me what I'm doing wrong?

Thanks

Andre · Accepted Answer · 2015-11-06 16:41:49Z

2

Listboxes in Access have a different set of properties/methods than listboxes in other Office programs (there they belong to the "Microsoft Forms" object model)

See https://msdn.microsoft.com/en-us/library/office/ff195480.aspx

In Access you simply add the multi-column data like you would in the RowSource property for RowSourceType = Value List, separated by semicolons.

With Me.lst_TemplateEditorList
    .AddItem "1;2;3"   ' first row
    .AddItem "4;5;6"   ' second row
End With

Edit

The Access 2010 Developer Help adds to the confusion: if you search for List, you get the Forms-Listbox and its properties, including .List. You have to look very closely to notice that you have left the Access realm and have entered the Microsoft Forms realm. German Access here, but you get the idea.

edited Nov 6, 2015 at 16:41

answered Nov 6, 2015 at 13:07

Andre

27.7k7 gold badges43 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Reynaldo Mantras · Accepted Answer · 2025-05-12 23:39:48Z

-1

Me.List833.ColumnCount = 3

Me.List833.ColumnWidths = "1 cm;4.8 cm;1 cm"

Me.List833.RowSourceType = "Value List"

Me.List833.AddItem ("1;2;3")

'You can also add items using variables

'Example: Me.List833.AddItem (Ttest1 & "; " & Ttest2 & "; " & Ttest3)

answered May 12 at 23:39

Reynaldo Mantras

1

2 Comments

dbc May 13 at 0:43

While this code may answer the question, might you please edit your post to add an explanation as to why/how it works as suggested by How do I write a good answer? Thanks!

Community May 13 at 12:42

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

Access VBA - adding row with multiple columns

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related