Multiple Selection Drop Down With Codes

multiple selection drop down

There is a sample file on my website that has VBA code for selecting multiple items from a data validation drop down list. You can insert all the selected items into the active cell, or down the adjacent column, or across a row. Today’s example shows how to make a multiple selection drop down with codes

Here is a simple multiple selection drop down – choose names from the drop down, one at a time. The latest name is added to the cell, with a comma and space used as a separator.

datavalmultiselectcode03

There is a video at the end of this article, that shows how the technique works.

Add a Matching Code in Adjacent Cell

Last week, someone asked it it was possible to put a code for each selection, in the cell to the right. For example, if you select a product in cell C3, put the product code in cell D3.

Here is the lookup table in the sample file:

datavalmultiselectcode01

The data validation drop downs are in column C, where you can select one or more numbers in any cell.

datavalmultiselectcode02

I created a variation of the original code, so it finds the matching ID for the selected number, and add that ID to the string in the adjacent cell in column D.

datavalmultiselectcode04

How the Code Works

In the sample file, this example is on the sheet named SameCellWithCode. To see the code, right-click on the sheet tab, and click View Code.

The lookup table is on the same sheet as the drop down lists, but you could do the same thing with a list on a different sheet. The lists of numbers and IDs are in named ranges, and referenced in the code.

    Set wsList = ActiveSheet
    Set rngList = wsList.Range("NumWordList")
    Set rngListID = wsList.Range("NumWordID")

Then, when you select a number, the code finds the matching ID, and puts it at the end of the string in the adjacent cell

         lCode = rngListID.Range("A1") _
            .Offset(Application. _
                WorksheetFunction _
                .Match(Target.Value, _
                rngList, 0) - 1, 0)
         Target.Offset(0, 1).Value = lCode

Download the Sample File

You can download the sample file from the Select Multiple Items page on my Contextures website. The zipped file is in xls format, and you’ll have to enable macros if you want to test the code.

If you copy the code into a different workbook, remember to check the code for sheet names, range names, row numbers and column numbers, and adjust those if necessary.

Video: Select Multiple Items in Drop Down List

You can see how it works in this video:

_______________

0 thoughts on “Multiple Selection Drop Down With Codes”

  1. Hi dear Debra
    Is it possible to insert any text instead of number in ID cells?
    For example, when I select “one”, instead of number “1”in the right cell, a word like “yellow” insert.
    I try your great code, but it works only for number!
    Thank you very much

  2. Hi Debra in the code for “Select multiple items from Excel data validation” can you specify by rows or alternating rows in the same column?

  3. Hi Debra also is there a way to turn the “Run-time error ‘1004’ for cells that do not data validation?

  4. Also is there a way to modify “data validation – selecting multiple items” on alternating rows (by cell0 that don’t contain validation?

  5. Can the multi selection be placed in cells going down ?
    I see it looks like they would be in next row but same cell ??

  6. Hi shahrdad/Debra
    That’s so great! Really thanks
    when I select “one”, number “1” is coming in next column D3
    then if i select “Two” in same row , “2” should come in D4
    Possible ???

Leave a Reply to Dennis Heggen Cancel reply

Your email address will not be published.

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