First of all, your formula is syntactically wrong. Paste it into a
cell directly, and Excel will bitch that the formula is no good. I'm
guessing that what you really want is

=AND((I7H7),MONTH(I7)=MONTH(H7),YEAR(I7)=YEAR(H7) ,I7DATE(YEAR(G7),MONTH(G7),DAY(G7)+21))

Now, what input are you validating? That is, what cell is the
validation applied to and what are some examples of valid and invalid
inputs?

Also, remember that validation is applied only when a cell's value is
changed by the user. If the cell is changed via VBA code, validation
rules are ignored.

Note, too, that it is possible to set up a set of custom validation
formulas in several cells that preclude any of those cells from being
changed. Essentially a mutually exclusive circular error.



Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Thu, 27 Aug 2009 14:37:51 -0600, salgud
wrote:

I've been working with data validation on a spreadsheet project I've been
doing. It seems that if I put custom/complex data validation criteria into
the Custom Formula field directly, they work fine. But when I put them in
using VBA, they no longer work properly - the won't validate correct data.

I've done this with a number of different formulas. E.g., the formula

=MONTH(H7)=MONTH(I7)

works fine entered by VBA. But, the formula

=and((I7H7),month(I7)=month(H7),year(I7)=year(H7 ),i7(month(G7),day(G7),year(G7)+21))

doesn't. It won't validate an entry that meets all these criteria.

I've done a fair amount of research online (as well as looking through my
various XL manuals) and haven't found an answer. I've used formulas that I
found online to see if it's just my formulas, and it's not.

So what's going on here? Is there a trick to entering formulae as
validation criteria via VBA that I'm missing?

I'm using XL2003. Thanks for the help.