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.

 

[Andrew Poulsom]

Have you tried replacing the semicolons in the formula with commas?

 

[svjensen]

I have now - but same result!

 

[svjensen]

The formula works fine when I insert it manually, but fails, as mentioned, when I try doing it with VBA.

 

[Andrew Poulsom]

When I inserted it manually Excel told me that it currently evaluated to an error. Do you get that?

It's hard to test without knowing the layout of your worksheet and what hovedmaterielmatrix refers to.

 

[svjensen]

Ok.
I have a matrix of lists which I need to use for dependent list in my data validation.
This is created along the lines of http://www.contextures.com/xlDataVal02.html#Illegal and http://www.contextures.com/xlDataVal02.html#Dynamic

The parent list is displayed in cell D7, and based on that choice the list in the dependant list is defined (using the formula in question).
It evaluates to an error, when no seelction has been made in the parent list.

I will se if I can upload the file somehow.

 

[svjensen]

Ok.
There is an example on the following link.

http://vinther-jensen.dk/excel/Example, dependent lists.xls

The sheet 'PP_Globaledata' contains the data lists with the NamedRanges which are used in the data validation formulas.

The sheet '1c' contains the dependent data validation lists.
Cells D7 and D8 have been set up manually to show you the functionality.

In cells D16 and D17 the second data validation needs to by added using VBA.

I hope this helps.

/Soren

 

[Norie]

Soren

I've downloaded the file and I'm looking at it now.

One question though, where are you actually trying to put this data validation?

You've used Selection rather than specifying a particular range.

I'm not saying that's going to solve the problem but it's usually a good idea to be specific about that sort of thing.

 

[Norie]

Actually I think I might have found the problem, and you might want to kick yourself.

It appears to be a simple typo, you have COUTIF instead of COUNTIF.

When I change that the code definitely works, though I couldn't say if it does what you want.

 

[Andrew Poulsom]

It worked for me when I corrected for a typo:

        "=OFFSET(INDIRECT(HLOOKUP($D7,hovedmaterielmatrix,2,FALSE)),0,0,COUNTIF(INDIRECT(HLOOKUP($D7,hovedmaterielmatrix,2,FALSE)&""Col""),""?*"")-2,1)"