Multiple select Listbox in Excel for an import sheet

Question

I am using Excel to store a list of products, that will be then used to import into a web application. One of the attributes of a product is colour, which is to be selectable from a defined list of colours. The issue is that there can be multiple colours for a product. At present, I am using a dropdown list, which is on each row for a product. This works, but is only available for one selected colour.

I have done some reading on the listbox control in Excel, but not sure how to apply this for every row, and how to save that back to the desired cell.

Answer 1

Multi-selects are tricky. Keep your approach as simple as possible.

Here's one alternative approach I've used before. Instead of a ListBox on each row, have the select event pop-up a form showing the available color options. The user selects one or more items in that list and the selected values are passed back to the cell's contents as a comma-separated string.

So, when the user clicks into the Color cell on a given row, the dialog pops-up like this:

Select colors for this product:

[] Red
[] Green
[] Blue
[] ...

<OK> <Cancel>

You have to write some code to handle that event and the user form, of course.

When OK is clicked, the selected items come back to the cell as something like, "Red, Blue, White". If Cancel is clicked, the cell is left as-is.

Good luck.