• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Prevent or Undo Typing in Data Validation Cells?

Cruiser

Member
Unchecking “Edit Directly In Cells” in my workbook performs as advertised - except in data validation cells. I have code to cancel before right click or double click, disable paste, and also to undo delete or backspace and replace my default entry. So the user is really locked in to selecting from the drop-down list - except in the one scenario where things get ugly;

Invalid entry is typed into the cell > error msg box pops up> user clicks “Retry”.

This is problematic because:
A - Calculations based on the DV selection return text in other cells. Clicking “Retry” causes these cells to return #VALUE! and ruin the otherwise professional look of the worksheets.
B - I want the users‘ options limited to selecting from the DV list. “Retry” simply adds an ugly step to an otherwise streamlined and professional looking process, making it necessary to delete, escape, or enter and then click “Cancel”.

And yes, my error msg box does say "Click Cancel", but we all know that will not stop anyone from clicking "Retry".

I need some code to prevent or undo ANY data entry in the cell.
Alternately, greying out “Retry” would be fine, but I believe that is impossible.

Thanks
 
If you limit the DV selection to a list
Then use formulas that don't show errors in the subsequent cells
Eg: =iferror(existing formula, "")
 
Thanks Hui. Seems so obvious now that you mention it. That's why you're a Ninja and I'm not! So that solves problem "A". Problem "B" is still very annoying, but I fear the solution is not so simple or obvious. I have determined that I do NOT want to make it impossible to Type in the DV cell because I need to allow the user access to the Delete and Backspace keys. These delete the current entry and replace it with my default entry via a worksheet change event based on Len = 0. I tried >=0, but then even selecting from the DV list causes the undo. I guess I need to figure out a worksheet change event that compares any string entered manually against the DV list and causes the undo if there is no match. Then I can just turn off error notification on the data validation and Bob's your Uncle. I have a couple of ideas, but any input would be most appreciated.
 
@Hui - This is now SOLVED. I added a Find code to my worksheet change events that did the trick. Thanks again for your help.
 
Back
Top