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.
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:
The data validation drop downs are in column C, where you can select one or more numbers in any cell.
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.
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: