Add New Items to Excel Drop Down List

There are a few sample files on my web site that let you automatically add new items to a drop down list. The most recent version works in newer versions of Excel, and uses named tables to store the lists.

When the code detects a new item, it asks if you want to add it to the drop down. If you click Yes, the item is added, and the lookup list is sorted in alphabetical order.

Lists on Multiple Sheets

The previous version of this file had all the lookup lists on a sheet named “Lists”. I’ve updated the code in the sample file, so now you can have lists on any sheet in the workbook – as long as the lists are in a named range.

Get the List Name and Range

There is Worksheet_Change event code on the data entry sheet, which automatically runs if you change a cell.

If the changed cell has a drop down list, the code gets the list’s name from the data validation formula. From that, the code can get the list range address and worksheet name.

  str = Target.Validation.Formula1
  str = Right(str, Len(str) - 1)
  
  Set rng = ThisWorkbook.Names(str).RefersToRange
  If rng Is Nothing Then Exit Sub
   Set ws = rng.Parent

Works on Protected Worksheet

This version of the sample file also works on a protected sheet, as long as the data entry cells are unlocked. However, the data entry table won’t expand if the sheet is protected, so you would need to have some blank rows for new items.

The previous version didn’t work on a protected sheet, because it tried to set a range based on Special Cells – Data Validation, and that’s not possible if the sheet is protected.

  Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
  On Error GoTo 0
  If rngDV Is Nothing Then Exit Sub

The new code just checks the cell for a data validation list, and exits the macro if there isn’t a list.

  If Target.Validation.Type <> 3 Then Exit Sub

Get the Sample File

To download the new version of the Data Validation Add New Items file, go to the instructions page on my website.

The file is in xlsm format, and contains macros. Be sure to enable macros if you want to test the file.

Video: Add New Items to Drop Down List

To see how the sample file works, you can watch this short video.

_______________