Creating data validation using VBA

[svjensen]

I need to update a large number of workbooks, and do this using VBA.

One of the updates is to add data validation to a given cell.
I have recorded a macro, which I am then trying to use, but I keep getting a 'Runtime error 1004 Application-defined or object-defined error'.

The recorded macro is as follows (I am using a Danish version of Excel, so the formula is with Danish functions):

   With Selection.Validation
       .Delete
       .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
       xlBetween, Formula1:= _
       "=FORSKYDNING(INDIREKTE(VOPSLAG($D7;hovedmaterielmatrix;2;FALSK));0;0;TÆL.HVIS(INDIREKTE(VOPSLAG($D7;hovedmaterielmatrix;2;FALSK)&""Col"");""?*"")-2;1)"
       .IgnoreBlank = True
       .InCellDropdown = True
       .InputTitle = ""
       .ErrorTitle = ""
       .InputMessage = ""
       .ErrorMessage = ""
       .ShowInput = True
       .ShowError = True
   End With

I suspect that the problem is the Danish functions, since I assume that VBA needs the English versions(?)

I have, therefore, tried replacing the formula with:
"=OFFSET(INDIRECT(HLOOKUP($D7;hovedmaterielmatrix;2;FALSE));0;0;COUNTIF(INDIRECT(HLOOKUP($D7;hovedmaterielmatrix;2;FALSE)&""Col"");""?*"")-2;1)"

But with the same result.

 

[svjensen]

I must have looked at that a thousand times!!
It sure would be easier working in an english version...

Thank you for your help

/Soren

 

[svjensen]

It turned out that the typo wasn't the actual fault. The problem was the fact, that no value had been selected for the parent list (cell D7 in my example), meaning that the data validation evaluated as an error.

I solved this by adding a value to D7, adding the data validation and then resetting D7. The codes then looks like:

    Range("D7").Value = "Våben"
    Range("D8").Select
    With Selection.Validation
       .Delete
       .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
       xlBetween, Formula1:= _
       "=OFFSET(INDIRECT(HLOOKUP($D7,hovedmaterielmatrix,2,FALSE)),0,0,COUNTIF(INDIRECT(HLOOKUP($D7,hovedmaterielmatrix,2,FALSE)&""Col""),""?*"")-2,1)"
       .IgnoreBlank = True
       .InCellDropdown = True
       .InputTitle = ""
       .ErrorTitle = ""
       .InputMessage = ""
       .ErrorMessage = ""
       .ShowInput = True
       .ShowError = True
   End With
   Range("D7").Value = vbNullString