VB Data Validation check
 

Hi all,
I am trying to find a way to check if a data validated cell contains invalid
data.

I know I can conditionally format it and that I can set Excel to Circle
Invalid Data but I want to check the validation status in VBA.

I was hoping that the following statement would return TRUE if the data was
invalid but it doesn't ..

mycell = activecell.Errors.Item(xlListDataValidation).value

I suppose another way would be to tell when Excel has circled the invalid
data. I know the command

activesheet.CircleInvalid

enables this but for the life of me, I can't find a property in
cells.validation that reflects the status.

Anyone have any ideas?

Grateful thanks as always!

Bony
--
"There are 10 types of people in this world. Those who understand Binary
and those who don''t ..."

VB Data Validation check
 

Perhaps there is a better way but you can try playing around with
Evaluate method.

A dummy sample:
This is the formula validation for H11:
=H11="a"

'Code:
Sub CheckValidation()
Dim rng As Range
Set rng = Range("H11")
With rng
If Evaluate(.Validation.Formula1) = True Then
Debug.Print "Validation passed"
Else
Debug.Print "Validation breached"
End If
End With
End Sub

So, if the value in H11=a, the code prints "Validation passed" else it
prints "Validation breached".

I don't know how complex your validations are but you can try this one
out.