Run Time Error adding Data validation to a excel

Question

I have a excel that im adding a data validation.

When I use the Data Validation process trough the excel function it works fine:

Custom Formula : =IF(AND(M7>=DATE(2015;1;1);M7<=D7);TRUE;FALSE)

But when i use VBA code to add that data validation the following error occurs:

Run-Time error '1004':

Application-defined or object-defined error

VBA:

Range("M7:M" & (6 + 15)).Select

With Selection.Validation
    .Delete
    .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="=IF(AND(M7>=DATE(2015;1;1);M7<=D7);TRUE;FALSE)"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = False
    .ShowError = True
End With

What im doing wrong ?

Thank you for the help.

vacip vacip · Accepted Answer · 2015-06-16T20:52:07

Replace all the ; (semicolons) with , (commas). The macro recorder is dim-witted sometimes. In the VBA code, you must use the US english Excel markup: . (dot) for a decimal, and , (comma) as parameter separator in formulas.

Like this:

.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _ 
 xlBetween, Formula1:="=IF(AND(M7>=DATE(2015,1,1),M7<=D7),TRUE,FALSE)"

Run Time Error adding Data validation to a excel

2 Answers