Data Validation

[salgud]

Trying to validate in the following scenario:
Cells H7 and I7 have dates in them (testing using 8/3/09 and 8/4/09).
Before an entry can be made in K7, I have a custom formula to check and see
that both dates in H7 and I7 are in the same month. The following code is
supposed to enter the validation criteria:

Public Sub DataValidationBDOCsmEDOC()

With Selection.Validation
.Delete
.Add Type:=xlValidateDate, AlertStyle:=xlValidAlertStop, Operator:= _
xlGreater, Formula1:="=MONTH(H" & lCurRow & ")=MONTH(I" & lCurRow &
")"
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "EDOC not same month as BDOC"
.InputMessage = ""
.ErrorMessage = _
"The BDOC and the EDOC are in different months." _
& Chr(10) & "Please correct these dates before entering the rates."
.ShowInput = False
.ShowError = True
End With

End Sub

The resulting custom formula in the Data Validation box is

=MONTH(H7)=MONTH(I7)

But when I put in dates of 8/3/09 and 8/4/09, it tells me they aren't in
the same month. When I put "=MONTH(H7)" and "=(MONTH(I7)" in other cells,
they both evaluate to 8. So why am I getting an invalid data message?

Thanks in advance.

 

[ker_01]

Salgud-

Try changing:
..Add Type:=xlValidateDate, AlertStyle:=xlValidAlertStop, Operator:= _
xlGreater , Formula1:="=MONTH(H" & lCurRow & ")=MONTH(I" & lCurRow
&")"

to
..Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=MONTH(H8)=MONTH(I8)"

I believe the issue is not with the formula, but the use of the date/greater
than parameters.

Please note that this still isn't perfect; the data validation appears to
allow a date in one cell and a blank in the other to still evaluate as 'True'
and allow data entry in K7. You may have to add a few conditions to your
formula to address this issue.

HTH,
Keith

 

[salgud]

Salgud-

Try changing:
.Add Type:=xlValidateDate, AlertStyle:=xlValidAlertStop, Operator:= _
xlGreater , Formula1:="=MONTH(H" & lCurRow & ")=MONTH(I" & lCurRow
&")"

to
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=MONTH(H8)=MONTH(I8)"

I believe the issue is not with the formula, but the use of the date/greater
than parameters.

Please note that this still isn't perfect; the data validation appears to
allow a date in one cell and a blank in the other to still evaluate as 'True'
and allow data entry in K7. You may have to add a few conditions to your
formula to address this issue.

HTH,
Keith

Thanks for your reply. As I said in my original post, the actual custom
validation formula inserted by the macro is

=MONTH(H7)=MONTH(I7)

which is the same thing (different row) as what you recommended. If I just
put the "7" in the code, all subsequent rows would validate based on the
dates in row 7, which is not what I want. I need to have the row be dynamic
for the spreadsheet to work, but so far, I have no indication that it's the
dynamic part that's causing the problem. The resulting formula is exactly
what I wanted it to be, it's just not validating valid dates. Or am I
missing something here?

 

[ker_01]

You can still use your custom row identifier per your original code. The only
thing you need to change are the 'type' and 'operator' variables in your VBA,
not the formula itself. I believe the issue is not with the formula, but the
use of the 'xlValidateDate' and 'xlGreater' parameters.

Again, you should still determine whether there are any circumstances where
a user might try to enter a value in Column K one (or both) of the date
fields is left blank, because I don't think your validation will stop entry
if one (or both) of the date fields are blank.

HTH,
Keith

 

[ker_01]

Add
..IgnoreBlank = False
to deal with the issue of blank date fields being ignored (not triggering
the validation).

HTH,
Keith

 

[salgud]

.IgnoreBlank = False

Thanks for the reply. It worked this time! It will now only accept an entry
if the 2 dates are in the same month.

But it will still accept blanks, unfortunately. I don't know if it can
check two cells for blank, so that check may have to be reserved for the
final check that is done after the spreadsheet is completed.

Thanks again.

 

[ker_01]

Try editing your formula to adapt the following logic (aircode)- you will
still have your dynamic formula, just change it to an "AND" condition, and
add the two final conditions using your dynamic row qualifier (lcurRow, if I
recall correctly)

=and(month(a1)=month(a2),a1>0, a2>0)

And keep the .ignoreblank = false line

That should do it