How to get multiple selected items(list in another worksheet 2) populated in a single cell

Hi,

I am stuck on a problem if anybody can help please.

Problem : I want to achieve the below functionality in excel 2007:

Step 1: The moment I click on A3 cell in worksheet1 , it will give me a list of names of the persons to choose from.
Note: This list of names of the persons is stored in worksheet2, for example list is [radhika,deepak,kamini,suraj].

Step2: So, from that list I can select one or many as per my requirement and the names which I select should appear in A3 cell only. for example , if I selected radhika & kamini, then A3 should contain both the values radhika & kamini seperated by a new line.

Step 3: One more thing, the drop down should only be visible the moment I click on A3 and once data is selected, drop down should disappear and values get populated in A3 cell separated by new line in the same cell if more than 1 value is selected.

Please if any excel expert can be help me on this, that would be very grateful.

Regards,
Anu

Is there not some confusion between A3 and B3 ...?

See here a possibility
Try and comment

Here an update in case there is NO selection

Hi,

It works..Thanks a ton for the help :-),

All the best!

Regards,
Anu

Hi,

Thanks for your help so far..If you can help me on more below thing that would be great.

Currently,the above code will show the form for A3 cell, if I want to show the same form and want the same functionality to be performed for A4,A5,B3,B4,B5..is there any reusable way in excel to do that like passing these cell address as parameter to commandbutton click function in that form? Or any good way to achieve this? So, that can avoid unnecessary creation of multiple same forms for each cell and it becomes difficult to manage as well if the cell count is high on which functionality is required.

Note: In my sheet, I will open another form also so can't make a generic click usage like initialize.I want only specific cells to open that form and other cella to open another form.

Regards,
Anu

Hello Anu,
Are cells "A3" and "A4,A5,B3,B4,B5" the only cells to work with or is it for all cells in sheet1.
In others words is the list limited?
PCi

See attached a new file with a more flexible macro.
The same UderForm is used for all working cells.
The selection of the cells is done in the sheet code

Anu,
It seems the story is done, it means just only the cells mentioned are needed; anyway if you have a look in the macro in sheet1's code you are certainly able to adjust it to your needs.
In fact your issue was a pleasant thing which woke up a topic (Multi selection Listbox) I did not work with since a long time
PCI