ListBox - Copy all selected options into a single cell seperated by commas

Hi,

I've got a userform with a listbox that i can select from multiple entries.

I need all the selected entries to be populated into the selected cell (user form opens when a cell is double clicked, therefore remains as the active cell) seperated by commas.

I.e. When cell J5 is double clicked, the userfor pops up, they select which options are required, click close and all the options are written into the active acell seperated by commas.

Additionally, if the cell is double clicked again, i'd like the the options to remain selected.

This is what i've come up with so for.

Thanks
AusTempest.

Damn, I thought that attached.

Apologies.

I'll boot my computer up and attach it

Sent from my One using Tapatalk

Example workbook attached.

For Example: If i double click in J5, my userform opens. If i select "ARH", "MRH" and "C-130", then clikc the OK and close button, i want cell J5 to be populated with "ARH, MRH, C-130" (order doesn't matter).

I also would like the three options to be reselected if the cell is double clicked again, so that if changes are neccesary, everything doesn't need to be selected all over again.

Awesome, thanks for that.

There's a comma at the end, but i don't really care about that.

Is there anyway to keep whats selected when re clicked?

That cuts off the first two letters of the output, not the last.

I.e. selecting "squirrel and "PC-9" gives ", uirrel, PC-9, "

HAHA !

Minorly changed you addition to remove the far right two characters instead (and also remove the ", " at the start if its blank)

Please Login or Register  to view this content.

Originally Posted by Izandol

I do not think you used all of the code I provided. However, it must be changed for empty cells to avoid starting cell with ", ":

Thanks for that. I've got the list box outputting correctly now.

Originally Posted by Norie

Put the selected values in an array, use Join to create a comma separated list from the array and then write the list to the cell.

To have the values in the cell selected when the userform opens read the list from the cell and use Split to separate the items into an array.

Loop through that array and use Application.Match to find the location of each item in the listbox and select it.

I would love to understand what your talking about, but i'm afraid it's a tad over my head.

This attachment is wht i've got so far.

I've modified the user form to list all in the left ListBox, and show added in the right ListBox.

In this scenario, i'd need the contents of the active cell to populate the right ListBox (Listbox2)

I've been googling what Norie said, but i can't seem to get my head around it.

Originally Posted by Izandol

You may try:

Please Login or Register  to view this content.

You Sir, are a life saver.

It works perfectly.

I've gone ahead and coded the two list boxes so that as one item is added to listbox2, its removed from listbox1, and vice versa, all good.

The only thing left is that when i first open the userform and use your code to populate listbox2 with what is already in the cell, i don't want those to start in list box one.

i use the following to populate listbox1 on opening

Please Login or Register  to view this content.

This is what i've got so far:

Please Login or Register  to view this content.

I've tried to select the elements of listbox2 and then remove them from listbox1 after both listboxes have been initially filled.

All i get is an opening error.

Is it possible to select and remove items before the Initialise sub has completed?

Thank you Izandol, thank you very much.

All done now.

Just curious though, do you know why my attempt didnt work? (and yes, i know your is a far better result)

That does make sense.

P.S. For anyone else finding this thread in the months / years to come, here is a copy of the final workbook.