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.

datavaladdsorttable06

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.

datavaladdsorttable07

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.

_______________

You may also like...

4 Responses

  1. sawan says:

    Hi,
    Is it possible to restrict the macro to only work with data validation within a column as opposed to all the cells thats got data validation?
    Thanks

  2. Nik says:

    Dear Debra,

    It works great! Is there any way of adding new items if one column is dependent on the other? eg: Column A: “Item” and Column B: “Description” – Column B is dependent on Column A selection. Eg:

    ITEM CONSULTANCY PLANTS EXPENSES
    Consultancy Drawing Plans Bursaria Spinosa Plan Printing B/W A1 Size
    Expenses Plan Revisions Goodenia Ovata Plan Printing B/W A4 Size
    Plants Prep Tender Docs Leucophyta Brownii Plan Printing CLR A1 Size
    Site Meeting Plan Printing CLR A4 Size

    If you wish to add another new plant to the Plants List, in Column A, select Plant, then the choice of 3 listed plants will show in Column B. However, Sarcoccoca Ruscifola isn’t listed, so we type it in, and the program automatically adds the new plant to the list more or less as per your macro.

    PLANTS
    Bursaria Spinosa
    Goodenia Ovata
    Leucophyta Brownii
    Sarcoccoca Ruscifola

    However, if I have a new Item to add to Column A, say SUNDRY, the program would not only have to add it to the ITEM list, but also create another column called “SUNDRY”, and add items (one-by-one as and when required)to it that are not listed and typed in Column B. All automatically sorted alphabetically of course!

    ITEM CONSULTANCY PLANTS EXPENSES SUNDRY
    Consultancy Drawing Plans Bursaria Spinosa Plan Printing B/W A1 Size Compost Bulk
    Expenses Plan Revisions Goodenia Ovata Plan Printing B/W A4 Size Waste Disposal
    Plants Prep Tender Docs Leucophyta Brownii Plan Printing CLR A1 Size
    Sundry Site Meeting Sarcoccoca Ruscifola Plan Printing CLR A4 Size

    Can this be done without too much reprogramming?

    Cheers,

    Nik

  3. Nik says:

    Sorry the tabulation did not work. I tried to upload a spreadsheet layout of what I mean but that didn’t work either, so I hope the following explains it better.

    The Columns should be as follows:
    ITEM (Column A)
    Consultancy
    Expenses
    Plants
    Sundry – added later Column A

    DESCRIPTION (Column B)
    Depends on input of Column A then Choice is from Column E, F, G or H for each line

    Column C & D left blank

    CONSULTANCY (Column E)
    Drawing Plans
    Plan Revisions
    Prep Tender Docs
    Site Meetings

    EXPENSES (Column F)
    Plan Printing B/W A1 Size
    Plan Printing B/W A4 Size
    Plan Printing CLR A1 Size
    Plan Printing CLR A4 Size

    PLANTS (Column G)
    Bursaria Spinosa
    Goodenia Ovata
    Leucophyta Brownii
    Sarcoccoca Ruscifola – input thru Column B and added to Column G

    SUNDRY – input thru Column A and Column H created for Sundry database items
    Compost Bulk – input thru Column B and added to Column H
    Waste Disposal – input thru Column B and added to Column H

  4. Flavio says:

    I tried your solution but I don’t know how to refer list of Items in another file used as source db for 2 different computer.
    How should I modify code?
    Thanks

Leave a Reply

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