Data Validation Excel 2007 entering via dropdown and from a form

Posted on 2011-10-19
Last Modified: 2012-06-27
I want a user to be able to enter data in a cell only if it is in the dropdown list so I set it up with data validation.  If the user tries to type in the cell something that is not in the dropdown he gets a warning and the data isn't changed when he selects cancel.
I also want the user to be able to double click on the cell and get essentially a larger list of values that can be entered into the cell.  If he picks something from the form list that is not part of the basic data validation, when he tries to leave the cell he gets a warning.   However, in this case, if he hits cancel it accepts the value entered by the form and continues on.  

I would prefer that when he uses the form to select a value that is not part of the drop down list, and he  is not presented with the warning.

Otherwise it should accpet the value that is either in the drop down list or in the form.  But he can't manually type in anything else.
Question by:StevenPMoffat

    Author Comment

    Forgot sample file
    LVL 85

    Accepted Solution

    Change the BeforeDoubleClick event so you cancel the default edit mode:
       ' Purpose: Open Up Activity Select Form
       If Target.Column = Activity.Column And Target.Row >= Activity.Row Then
          Cancel = True
       End If

    Open in new window


    Author Closing Comment

    Works, but I don't really know why.  A small explanation would be helpful.

    LVL 85

    Expert Comment

    by:Rory Archibald
    Two points to consider:
    1. Entering a value in code bypasses data validation
    2. When you double-click a cell, the default action is to enter edit mode in that cell.

    In your original code, the form was entering the value with no problem or message (see #1) and then closing.

    At that point, the default action occurred (#2) and you ended up in edit mode in the cell.

    When you try and leave it, the validation kicks in - just as if you had typed the value in manually - and you get the message. (the reason the value was accepted when you press escape is because that was already the existing value in the cell, so when DV cancelled your entry, it simply put back whatever was there before, even if that happened to be invalid)

    By setting Cancel = True, the cell never goes into edit mode and you don't have the error message.

    Does that make sense?


    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    Suggested Solutions

    I've recently been in need of an Excel macro that could add a letter before the text on multiple cells in an Excel document. My English is as it is, so I will try explain what it does diffrently. If you have an excel document with 2000 rows an…
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now