Taking values from drop down into one cell

[tinkerbelle]

Hi all, hope someone can help start off with this VBA, see attached file.

See the sheet "Data". Ideally the user would select B5 for example and get a drop down / box they could select multiple values from, the items in the drop down box would be those in the "Options" sheet and specifically B7:B16. When selecting in C5 they would get a selection from the "Options" sheet and C7:C22

The cell in B5 would then contain the data the user selected, so it could contain "Item 1 - 1, Item 1 - 3 , Item 1 - 10" for example.

Hope to hear from you.

Many thanks
tinkerbelle

[Eastw00d]

You dont have to do this with VBA, simply use datavalidation:
Select cells in which you want the drop-down list to appear. (In your case Data - B5:B8)
Excel Data Validation
On the Data tab, in the Data Tools group, click the Data Validation button, then select the Data Validation menu option.
On the Settings tab, set the Validation criteria, Allow: criteria as List.
Click in the Source field then select cells that contain the list items (or type the items in the source field, separated by commas). (Options - B7:B16)
Make sure that both the Ignore blank and In-cell dropdown check boxes are checked.
Click OK.
and likewise for the next group
Cheers
Erwin

[davsth]

As the above but select b5 as the cell
select data validation as a list and put the source as =Options!B$6:B$22

you can now copy this cell to the rest of your table b5:i8

I included 6 too give you blanks as the first value and deliberately made all the ranges to 22 as you copy it sideways the Bs become Cs etc

[tinkerbelle]

hi, thank you both so much.

yes i would have used data validation, but i want the user to be able to enter more than one value into the cell. eg: The cell in B5 would then contain the data the user selected, so it could contain "Item 1 - 1, Item 1 - 3 , Item 1 - 10". If this can be produced without VBA that would be great.

Thanks!

[davsth]

missed the multiple, its usually a pain to do, especially if you subsequently want to use the values
https://trumpexcel.com/select-multip...wn-list-excel/

or

https://www.contextures.com/excel-da...n-listbox.html

are a start

[tinkerbelle]

Davsth - oh great thanks, just looking at contextures, Debra seems to have a process to follow - thanks again! Luce