VB Data Validation check

B

Bony Pony

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
 
A

AB

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top