Automatically Add New Items to Excel Data Validation Drop Down

There’s a sample Excel workbook on my Contextures website that uses a bit of Excel VBA to automatically add new items to an Excel data validation drop down list.

For example, if the drop down list shows Apple, Banana and Peach, you can type Lemon in the data validation cell. Then, as soon as you press the Enter key, Lemon is added to the named range that the data validation list is based on. The source list is sorted too, so that Lemon appears between Banana and Peach.

datavaladd01

Read the Instructions

Someone emailed me last week, and asked if I would explain how the Excel VBA code works. It rained (and even snowed a little) on Friday, so it was a good day to stay in, and work on a new page for the website.

If you’re interested in setting up a similar file, you can wander over to the Contextures website, and read Excel Data Validation – Add New Items.

Watch the Video

There’s a short video that demonstrates the file, and briefly explains how the code works. You can watch that, if it’s too early in the day to read about Excel VBA code.

Download the Sample File

If you’d rather just play with the file, and figure it our for yourself, you can download the workbook from my Contextures website. Go to the sample workbooks page, and in the data validation section, look for DV0021 – Update Multiple Validation Lists

It’s in Excel 2003 format, and contains macros. You can enable macros if you want to test the code.

___________

You may also like...

20 Responses

  1. Data Validation in Excel « Excel Tips Monster says:

    […] This article at Contextures shows how to automatically add a new item to the list. […]

  2. Kanti Chiba says:

    This post is more than just adding items to a validation list, it is an excellent tutorial in VBA techniques and wonderful example of elegant programming.

    The number of lessons learnt here are too numerous to mention.

    Thank you very much for this post

  3. Bob G says:

    Excelent as always. You should do a video on how you make these videos.

  4. Nik D says:

    Great way to learn! Excellent Tutorial! It is very much appreciated!

    Now, how do we modify it to “Add New Items” to Dependant Drop Down Lists?

    Cheers!

  5. Reza says:

    Awesome….

    Is it possible using formula instead a macro. I don’t know much about macro.

    Thank you

  6. Wynand says:

    Just one related Q
    In your tutorial ‘validation combo box click’ you provide additional code allowing tab and enter.
    I get an error on the sample file when I tab but not when I ENTER, any ideas what the problem could be?

  7. Wynand, what version of Excel are you using, and what error do you see?

  8. wynan says:

    Excel 2003 SP3 does not give any problems. I use an older version (not sure which) as well that closes down Exel as soon as I hit Tab, with the normal dialogue ‘send error report, don’t send’ )Enter works fine in the older version

  9. Matthew Redekopp says:

    This is great! Thank you for your seay to follow video and sample file. I was wondering if you are aware of a quick key for adding data validation to a cell that you are working on. Not a QK for bringing the list down (ie the list already exists in the cell) but to make a cell a data validation cell without using the menu. Ideas? Thanks again!!!

  10. Nik D says:

    Add New Items – Works a treat! Thank you Debra! Cheers

  11. Nik D says:

    Hello Debra!

    Following on from “Adding New Items”, is it possible to do the same, if Column C (Fruit) is dependant upon the Item Selected in Column B (Client).

    Say I have a new client, Mary, so she needs to be added in to the client list, and she likes all five fruits (Apple, Banana, Lemon, Orange and Peach), but she also likes Pears, so that fruit needs to be added to the list. How do we go about having such a facility.

    It may be that Bea only likes Apples & Lemons, Al likes Lemons, Oranges & Peaches and EM likes Apples, Bananas & Peaches, and when we select each client it only shows what they like. So with a new client, we select from an existing list of fruits and add those that are not on the list. Those not on the list are then added to the main list.

    I hope you understand what i’m trying to do! Cheers

  12. Bryan McGinty says:

    How am able to take the codes in the template to add directly into my drop down list so I can add new items to my drop down list

  13. Charlene says:

    Is it possible to restrict the target range to column 2? I’m running a different code on another set of cells with data validation. When I’m working with the second set of DV cells the code errors out at
    If Intersect(Target, rngDV) Is Nothing Then GoTo exitHandler
    I tried adding this:
    If Target.Column 2 Then GoTo exitHandler
    When that didn’t work I changed this:
    If Target.Row > 4 Then
    to: If Target.Column = 2 Then
    but it still errors out.

    Thanks for all your great help, all the time!
    (I hope I tagged the code properly)

  14. Charlene, if you’re trying to restrict this to column 2, then the following should work:
    If Target.Column = 2 Then

    Or, if you want something different to happen in columns 2 and 4, you could use Select Case:
    Select Case Target.Column
    Case 2
    ‘column 2 code here
    Case 4
    ‘column 4 code here
    End Select

  15. Charlene says:

    Works perfectly: If Target.Column = 2 Then

    Thanks Debra!

  16. kfong says:

    One of the greatest webs to learn Excel and VBA….Thanks…Please keep going on….

  17. Tom says:

    Very instructive … would it be possible to also add a means so that all entries are captilized?
    I did try to insert in the code for both the working sheet and the list sheet the following:
    Target = UCase(Target) … but it kicked up and error?

  18. KimJK says:

    Try as I may, I cannot get this to work. I tried first to use it in an existing workbook–no go. So I created a new workbook that I believe is a perfect clone of your sample. The drop down lists operate but any attempt to add a new item to either list fails in error. Any ideas?? Thanks for your wonderful site–can’t tell you how much I’ve learned.

  19. Udaya726 says:

    I am using this code and works fine. But its not validating the invalid data. If user types a value not in a drop down it accept. It may due the COUNTA function.Can you suggest to show error message when user types invalid data

  1. June 2, 2013

    […] if you are using Excel 2003 or earlier, see this Contextures post for a non-table way of automatically adding items to a data validation list. You could easily add […]

Leave a Reply to Matthew Redekopp Cancel reply

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