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 ..."

I'm not sure what you're doing with the Post title but i replied to
your previous post as well.
Am replying here too, in case it could still be of use.

You can try using Evaluate method. Read Help on it.

Sample:
I've got Validation formula for H11:
=H11="a"

So, if the H11 has any other value but "a", the sub below prints
"Validation breached" otherwise prints "Validation passed".

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

thisworksheet.CircleInvalid

End Sub



On Oct 8, 10:47*am, Bony Pony
wrote:
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 ..."

Hi,
Nice try but it's not working the way I need it to. I use named range lists
as a validation source and checking a formula isn't the method.

Thanks anyway!

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


"AB" wrote:

I'm not sure what you're doing with the Post title but i replied to
your previous post as well.
Am replying here too, in case it could still be of use.

You can try using Evaluate method. Read Help on it.

Sample:
I've got Validation formula for H11:
=H11="a"

So, if the H11 has any other value but "a", the sub below prints
"Validation breached" otherwise prints "Validation passed".

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

thisworksheet.CircleInvalid

End Sub



On Oct 8, 10:47 am, Bony Pony
wrote:
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 ..."