What happens to data validation when created from VBA?

[salgud]

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((I7>H7),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.

 

[Chip Pearson]

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((I7>H7),MONTH(I7)=MONTH(H7),YEAR(I7)=YEAR(H7),I7>DATE(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)

 

[ker_01]

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

doesn't evaluate for me at all, even when pasted directly into a worksheet.
When I change it to

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

It recognizes it as a valid formula, but using today's date as a sample for
G7, the last part of the formula returns [date(8,27,2030)] whereas the date
formula actually requires them in reverse order [date(year, month, day)] so
your formula returns a value of 9/20/1915 for G7 (cell i7 will always be
larger, unless you are working with pre-1915 dates).

I didn't get as far as entering these into the custom validation box for
additional testing, so you'll still need to do that after verifying that your
formula validates per your expectations.

HTH,
Keith

 

[salgud]

Thanks for your reply.

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((I7>H7),MONTH(I7)=MONTH(H7),YEAR(I7)=YEAR(H7),I7>DATE(YEAR(G7),MONTH(G7),DAY(G7)+21))

I think you guessed wrong. (Hard to guess right if the formula is wrong).
G7 is the client DOB. The situation is that H7 and I7 contain the dates
that service started and finished. The criteria are:
1. I7>H7 (service ended after it started)
2. Both must be in the same month and year (invoiced monthly)
3. I7 must be less than 21 yrs after G7 (service is for people under 21
only)

I grabbed this one in a hurry when I was posting, but it may not have been
one of the ones I had this particular problem with. I'll test before I post
next time.

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?

See above. There are a number of others where I've had the same problem to
the point where I'd pretty much given up on these kinds of complex
validations being entered via VBA. None of the ones I tried, even with help
here in the newsgroup, worked.

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.

I had figured that much out. On cells where the data is calculated, I don't
have any validation. Only on cells entered by the user.

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.

Not possible here. I did the validations from left to right, as the users
will be entering the data that way. So I'm always validating a cell to the
left of the current cell.

Here's another example of one I could never get to work. It goes in cell
D7, and checks to see that the client ID (the first being in cell B7)
consists of 7 characters, one letter followed by six numbers (A123456).

=AND(LEN(B7)=7,ISNUMBER(MID(B7,2,6)*1),CODE(LEFT(UPPER(B7),1))>64,CODE(LEFT(UPPER(B7),1))<91)

This formula works fine when put directly into the cell, but when I put it
in code, I get an Object defined error.

With Selection.Validation
.Delete
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:=
_
xlGreater,
Formula1:="=AND(LEN(B7)=7,ISNUMBER(MID(B7,2,6)*1),CODE(LEFT(UPPER(B7),1))>64,CODE(LEFT(UPPER(B7),1))<91)"
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "bad"
.InputMessage = ""
.ErrorMessage = _
"bad" _
& Chr(10) & "Please correct these dates before entering the rates."
.ShowInput = False
.ShowError = True
End With

Someone suggested I remove the second = sign (in front of the AND). That
gets rid of the object defined error, but then when I test it, it tells me
that a valid Client Id isn't valid and allows the entry.

I have others, but I'll stop here and see if you, or someone, can tell me
what's wrong with this one. Maybe it's the same thing with the others.

Do you see what I'm missing?