Error 1004 when using data validation drop-down list

0
votes

I have problem when I use the validation data drop-down list. I’ve registered the macro that should show drop-down list if a conditio is met (drop down list is based on a named range). When i type this manually and add data validation to the selected range, it works fine, but when I try to use macro, it fails and show the Error 1004 at line .Add. It is also imposible to register macro and run it after. Here is the code:

Dim kolQ as Range
Set kolQ = (here is range in column Q)
With kolQ.Validation
.Delete
.Add Type:=xlValidateList, Formula1:="=IF(AND($L2<DATE(2014;5;1);$M2>DATE(2015;4;30));list_a;list_b)"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "No valid status!"
.InputMessage = ""
.ErrorMessage = "Chose option from drop-down list!"
.ShowInput = True
.ShowError = True
End With

I’ve searched trough the forum and google about that, but all the answers I’ve found don’t work in this case. The named ranges (list_a, list_b) exists in another sheet of workbook.

3
excelvalidationdrop-down-menuvba

3 Answers

1
votes

Try changing ; to , in your Formula1.
Also, try incorporating CHOOSE function. Something like:

Formula1:="=CHOOSE(IF(AND($L2<DATE(2014,5,1),$M2>DATE(2015,4,30)),1,2),list_a,list_b)"
0
votes

When writing formula like this, local region formatting gets ignored, so semi-colons that work fine in a cell will fail. You'll need to alter your formula from:

.Add Type:=xlValidateList, Formula1:="=IF(AND($L2<DATE(2014;5;1);$M2>DATE(2015;4;30));list_a;list_b)"

To:

.Add Type:=xlValidateList, Formula1:="=IF(AND($L2<DATE(2014,5,1),$M2>DATE(2015,4,30)),list_a,list_b)"