VBA to "Exit Sub" when "Data Validation" error is triggered

EssKayKay 0 Reputation points

I have a cell with assigned Data Validation criteria. If the user inputs an erroneous value it returns the “Stop” Error Alert with the Retry/Cancel/Help buttons.

Upon entering data in the cell another routine is triggered. Is it possible that if the user selects the “Cancel” option the remaining routine would also be cancelled? That is, something like Exit Sub upon selecting Cancel.



My current issue is in regards to Data Validation criteria.  I have a cell with an associated dropdown list.  All works fine if the user only selects an option (date) from the list.  However, I want the user to also be able to “keyin” a date.  Again this is fine if the user keys in an acceptable date.  However, if the user keys in an unacceptable date (i.e., one that is not in the list) the Data Validation’s “stop” error message pops up with the Retry/Cancel /Help buttons, which is great.  However, what I want is if the user selects “Cancel” I want the routine to truly stop (i.e., something like an If/Then with an Exit Sub). 

My question – is there a way to stop/exit a Sub-Routine if/when the user clicks on the Data Validation’s Stop error’s “Cancel” button? 


A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,077 questions
Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,034 questions
0 comments No comments
{count} votes