VBA to populate row of cells with userform listbox data (code runs by column only)

[bifjamod]

I have a userform with a listbox, for which I want the selected items to populate to cells in row 3, starting in column C. The below script populates cells in column C, starting in row 3 (in other words, down, instead of across.) I've tried multiple tweaks to solve this, to no avail, and am asking the members here for an assist. Credit for the original script belongs to a forum post somewhere (not sure if this forum or another - forgot to bookmark/document the page.)


For I = 1 To ListBox2.ListCount
For j = 1 To ListBox2.ColumnCount
ActiveSheet.Cells(I + 2, j + 2) = ListBox2.List(I - 1, j - 1)
Next j
Next I

It seems to me the column count is irrelevant, as there is only one column of data in the listbox.

Excel 2013 / Win10Pro

[Sebastian H]

Why don't you just exchange the references to rows and columns, like this:

ActiveSheet.Cells(j + 2, i + 2) = ListBox2.List(i - 1, j - 1)


It seems to me the column count is irrelevant,
Correct, you don't need the For j loop when your ListBox only has one column.

But the functionality of your code is not what you describe; this lists all rows of the ListBox, regardless what the user selected. If you want that, you need to add a condition like this:

If ListBox1.Selected(i - 1) Then ...

[bifjamod]

Thank you! So often it's so obvious! Thing is, I'd swear I tried that, without success. I must have done it incorrectly if I did.

As for your second point, on the all / selection - the user form actually has two listboxes, one which contains all the available options, and one which contains the selections. This is why the script shows ListBox2. Probably not necessary to do it that way, but the sample I got the script from did it that way, and I kind of like it.

Side note: I also took out the j loop and changed the j reference to hard-coded numbers. Works perfectly.

Thanks again for your help!

[Sebastian H]

You're welcome. I know how it is when you're working in an unfamiliar environment: you try out 5 different things, each with 2 possibilities, and you end up with 32 variations, some of them you try twice, and the one that really matters gets forgotten.