VB Data Validation check - apologies for incorrect post
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 - apologies for incorrect post
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 ..." |
VB Data Validation check - apologies for incorrect post
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 ..." |
All times are GMT +1. The time now is 04:36 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com