Multiple Selection Drop Down With Codes

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.

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:

Or watch on YouTube: Select Multiple Items from Excel Data Validation List

_______________

You may also like...

8 Responses

  1. shahrdad says:

    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. Shahrdad says:

    Hi Debra
    That’s so great! Really thanks

  3. Emmy says:

    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?

  4. Emmy Carmona says:

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

  5. Emmy Carmona says:

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

  6. Dennis Heggen says:

    Can the multi selection be placed in cells going down ?

    I see it looks like they would be in next row but same cell ??

  7. Prasanna says:

    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

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