Select Cell Items From ListBox

Select Cell Items From ListBox

I’ve posted a few techniques for selecting multiple items from the drop down list in a cell. In the screen shot below, there is a drop down list in cell C3. When you select an item from the list, it’s added to the cell, instead of overwriting the previously selected value. Now, instead of a cell drop down, here's how to select cell items from ListBox.

This screen shot shows a cell drop down that is set up for multiple selections.

multiselectlistbox01

My new method combines that multiple selection technique with the combobox popup technique from one of my other data validation examples.

In this combo popup screen shot, there is a combo box over cell B3, with a list of Weekdays. There is only one hidden combo box on the worksheet, and it appears when you click on any cell that has a data validation list.

multiselectlistbox02

Show a Listbox

In my new method, when you click on a cell that has a data validation list, a listbox appears. It shows all the items from the source list, with check boxes, so you can select multiple items.

The listbox is embedded in a UserForm, which also has OK and Cancel buttons.

multiselectlistbox03

When you click OK, all the selected items are added to the cell, separated by a comma and space.

multiselectlistbox04

Listbox Instead of Combo box

I used the listbox, instead of a combo box, because the listbox makes it easier to select several items, by adding check marks. You can see several options at a glance, and scroll down, if the list is long.

In the next screen shot, there are 1100 entries in the ItemList, and you can quickly scroll through them all in the Listbox.

multiselectlistbox05

Download the Sample File

To see the code, and experiment with the Listbox, you can download the sample file from my Contextures website.

The zipped file is in xlsm format, so enable macros when you open the file.
__________________________

0 thoughts on “Select Cell Items From ListBox”

  1. This is very useful. Thank you for making this available. I'd like to do SameCellAddRemove with a line break, but I can't figure out how to do it. Do you have any suggestions?

  2. Is there a way to utilize this, limit the selections to say 5 and requiring 5 and instead of placing in one cell with the seperator of choice, place the the 5 entries in adjoining cells on a row?

  3. Hi
    I created the item nos with item names. I want from drop down list like when i click the cell it shows only item name in drop down list, but it should appear item number only on cell. How can I make it?
    e.g:
    no:1 AAA
    no:2 BBB
    when I click no:1 in drop down list, in cell it appear "AAA" only

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.