Data validation using code

  • Thread starter Thread starter Gareth
  • Start date Start date
G

Gareth

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<=K2,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
 
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(ISBLANK(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
 
Back
Top