Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 ..." |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
data validation invalid in dynamic validation list | Excel Discussion (Misc queries) | |||
data validation invalid in dynamic validation list | Excel Worksheet Functions | |||
Data validation with validation lists and combo boxs | Excel Discussion (Misc queries) | |||
Problem with validation check! | Excel Worksheet Functions | |||
Check Boxes & Data Validation | Excel Discussion (Misc queries) |