Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
validate data using code
I have the following code which works fine on range dob:
With Range("dob").Validation .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, _ Operator:=xlBetween, _ Formula1:="=AND(I2=29221,I2<=TODAY(),I2<=J2,OR(IS BLANK(K2),I2<=K2),OR(ISBLA NK(L2),I2<=L2))" .IgnoreBlank = False .ErrorTitle = "Date of birth" .ErrorMessage = "Entry must be a date between 01/01/1980 and today." & Chr(10) & Chr(10) & "It cannot be greater than either the on, off or died date." End With Range dob does however contain blank cells and these show up as validation errors. Is there any way to amend the code so as not to use .IgnoreBlank = False, which I think is causing them to show as errors? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
validate data using code
You can change the formula:
Formula1:="=OR(ISBLANK(I2),AND(I2=29221,I2<=TODAY (),I2<=J2,OR(ISBLANK(K2),I2<=K2),OR(ISBLANK(L2),I2 <=L2)))" Gareth wrote: I have the following code which works fine on range dob: With Range("dob").Validation .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, _ Operator:=xlBetween, _ Formula1:="=AND(I2=29221,I2<=TODAY(),I2<=J2,OR(IS BLANK(K2),I2<=K2),OR(ISBLA NK(L2),I2<=L2))" .IgnoreBlank = False .ErrorTitle = "Date of birth" .ErrorMessage = "Entry must be a date between 01/01/1980 and today." & Chr(10) & Chr(10) & "It cannot be greater than either the on, off or died date." End With Range dob does however contain blank cells and these show up as validation errors. Is there any way to amend the code so as not to use .IgnoreBlank = False, which I think is causing them to show as errors? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code/Function to Look at Previous Entry to Validate Data | Excel Worksheet Functions | |||
validate data using formulae | Excel Worksheet Functions | |||
Validate Data Message Box | Excel Discussion (Misc queries) | |||
Validate cells have data | Excel Worksheet Functions | |||
How to validate in code the contents of a cell? | Excel Programming |