I am currently building an Excel database that uses WorksheetChange to perform several automations when a user enters data, one of which is Application.Undo. However, I also have data validation cells. When I input an invalid entry into a cell with data validation and click "Cancel", the cell is returned to its previous state and then the WorksheetChange function is run, where I get an Application.Undo Error due to whatever code was run for the data validation cell. Is there a way to determine if data validation has occurred?
When I try using Target.Validation.Value, it always returns True because the data validation has already reset the value to its previous state, which is a valid value. What I think would be the easiest solution is to have code like this:
If Target Cell Data Validation Occurred And Was Invalid Then Exit Sub End
Obviously, I am looking for what code might allow me to make this If statement. Any help is appreciated!
When you set up the Data Validation, if you uncheck the option for "Show error after invalid data is entered" then you can check Target.Validation.Value in the change event handler. It will be False if the user has entered an invalid value, and you can handle that case as you want.