Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data validation using code
I have the folowing code which works fine when there is data in all the
cells referenced. Unfortunately, column's K and L do not always have data in them (all cells are date cells). This causes the validation not to work. With Range("dob").Validation ..Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, _ Operator:=xlBetween, Formula1:="=AND(I2=29221,I2<=TODAY(),I2<=J2,I2<=K 2,I2<=L2)” ..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 Is there a way around it? Many thanks. Gareth |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data validation using code
You can remove the check mark from Ignore Blanks, and revise your
formula slightly: 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(ISBLANK(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 Gareth wrote: I have the folowing code which works fine when there is data in all the cells referenced. Unfortunately, column's K and L do not always have data in them (all cells are date cells). This causes the validation not to work. With Range("dob").Validation .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, _ Operator:=xlBetween, Formula1:="=AND(I2=29221,I2<=TODAY(),I2<=J2,I2<=K 2,I2<=L2)” .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 Is there a way around it? Many thanks. Gareth -- 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 | |||
Zip Code Validation - length =5 or 9 | Excel Discussion (Misc queries) | |||
Code to find data validation in a cell | Excel Discussion (Misc queries) | |||
Validation code for macro to run | Excel Discussion (Misc queries) | |||
Add validation using sheet code | Excel Programming | |||
Validation of time in code | Excel Programming |