[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Data Validation Excel 2007 entering via dropdown and from a form

Posted on 2011-10-19
Medium Priority
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
  • 2
  • 2

Author Comment

ID: 36996617
Forgot sample file
LVL 85

Accepted Solution

Rory Archibald earned 1000 total points
ID: 36999022
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

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

LVL 85

Expert Comment

by:Rory Archibald
ID: 37004903
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

872 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