VBA Excel: Evaluate Data Validation Rule

Question

I am wondering if there is a clean way to evaluate in-cell data validation. It could be either a length check or a drop-down list etc.

I can easily add data validation rules using the below code that validates if the entry is more than 5 characters in cell A1, but is there a way to evaluate it from inside VBA?

With Cells(1,1).DataBodyRange.Validation
    .Delete
    .Add Type:=xlValidateTextLength, AlertStyle:=xlValidAlertStop, _
         Operator:=xlLess, Formula1:="6"
    .ErrorTitle = "Too Many Characters"
    .ErrorMessage = "The maximum allowed number of characters For this field is 5"
    .ShowError = TRUE
End With

Can you explain what you would like to achieve? How does "evaluate [data validation] from inside VBA" manifest? DV is used for data entry. If the user meets the DV criteria, the entry is accepted. If they don't, the value cannot be confirmed in the cell. What would you want to do in VBA there? What should it do? — teylyn
– teylyn, Commented Sep 13, 2020 at 22:28
Or do you want to write your own data validation with VBA? In that case, look at Worksheet Change events. — teylyn
– teylyn, Commented Sep 13, 2020 at 22:29
When you set the cell's validation, Excel creates a Validation object for the range and sets properties for that object. In reverse, you can read the properties of an existing validation object, like Debug.Print Cell.Validation.Formula1 — Variatus
– Variatus, Commented Sep 14, 2020 at 0:56

teylyn · Accepted Answer · 2020-09-13 22:32:48Z

0

If you don't want to use Data Validation and prefer to re-invent that wheel with VBA, you can use a Worksheet_Change event.

Let the code evaluate the target cell and determine if it meets your criteria. If it doesn't, clear the target cell and display a message.

This approach will effectively remove the user's ability to use Undo, because the VBA will run after each data entry on the whole sheet and will clear the Undo stack. You may want to keep using the built-in Data Validation if you want to retain usability for the end users.

answered Sep 13, 2020 at 22:32

teylyn

36.2k6 gold badges58 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA Excel: Evaluate Data Validation Rule

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related