Selecting Multiple items from a list in one cell.

Good mornngs,

I've searched for a topic to cover this but could not find one so....

Is it possible to restrict the values of cells in a particular column to entries defined in a list BUT to allow each cell in that column to display multiple items from the list (seperated by a comma for example)


I've attached an example of what I would like to do -

In Sheet "2010 Data" I want to be able to select multiple values in column F....(the values are defined within the list named "Platforms" on the worksheet called "Lookups"

Thanks in advance !

dan

Not with Data Validation, no. You're looking at using other controls in truth - like a ListBox in Multi Select mode... how you invoke that is up to you.

In the attached I added some bits to your file which basically mean that when you double click on a cell in F you're presented with a Multi Select ListBox - on confirm your selections are written back to the cell in which you double clicked.

You could add more to it such that when initialised it reflected current selections in the cell (if any) but it might give you some pointers nonetheless.

Originally Posted by djtayla

where can I see the code you have used to do this ?

If you right click on '2010 Data' tab in XL and select View Code you will be taken to the VB Editor and more specifically to the Sheet Object in which the double click event resides.

Below the Sheets you should see a folder called "Forms"
If you expand that you should see UserForm1
Double clicking on UserForm1 should in turn show the UserForm in the style in which it will display.
Pressing F7 (or using View Menu - View Code) will show the underlying code associated with the UserForm controls -- specifically what happens when you click on the Confirm button.


Goes without saying that explaining VBA is not a trivial exercise but I can outline the workflow:

-- Double Clicking any cell on 2010 sheet invokes the DoubleClick event...

-- If the cell that was double clicked is in Column F it will in turn Display the UserForm
(I should really have added a row check to exclude header)

-- The UserForm comprises two controls - a ListBox (sourced from Platforms range) and a Button

-- The ListBox is set to MultiSelect mode "doing what it says on the tin"

-- When the Confirm Button is pressed the Click event for that Button is invoked (the code you see via F7 on the UserForm)

-- When the Button is pressed the code iterates the Items in the ListBox looking for those that were selected and joining them in a string which is subsequently written back to the cell that was double clicked

-- UserForm at this point is "unloaded" from View and the Double Click event ("edit cell" action) is cancelled.

Using my earlier sample...

Change the DoubleClick Event to:

Please Login or Register  to view this content.

so at this point the UserForm will be displayed for both F & G

then add a routine to the UserForm module itself:

Please Login or Register  to view this content.

so when the UserForm is invoked the RowSource for the ListBox is modified based on which column is active (F or G)