add validation list in excel via macro

[script883]

Ik zou graag een validation list willen toevoegen aan een cell om zo een in-cell dropdown te krijgen. De named lists staan op een aparte scheet. in begin maak ik gebruik van een inputbox om te vragen welke named list te gebruiken.
Als bron voor de validation list maak ik gebruik van een formule om zo met dynamische lijsten te kunnen werken.
De formule is : =VERSCHUIVING(GroepNaam;0;0;AANTALARG(GroepNaamCol);1)
Groepnaam is eerste cel van een kolom
GroepnaamCol is de hele kolom waarvan Groepnaam de eerste cel is

Wanneer ik deze manueel aanmaak (zo heb ik ook de eens een macro opgenomen) met de exacte formule zoals hierboven werkt het. Wanneer ik de macro run (zelfs diegene die ik opgenomen heb) krijg ik steeds dezelfde fout. namelijk :

Door de toepassing of door object gedefinieerde fout

De opgenomen macro is :

Sub Macro1()

With Selection.validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=VERSCHUIVING(GroepNaam;0;0;AANTALARG(GroepNaamCol);1)"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub

De macro met inputbox is

Sub add_drop_down()

lijstNaam = InputBox("input lijstnaam", "Geef lijstnaam")
formule = "=OFFSET(" & lijstNaam & ";0;0;COUNTA(" & lijstNaam & "Col);1)"
With Selection.validation
.Delete
.Add Type:=xlValidateList, Formula1:=formule
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

End Sub

Beiden geven diezelfde fout op de regel
.Add Type:=.........

Weet iemand hier soms een oplossing voor? alvast bedankt op voorhand

 

[Ginger]

Script883, Ik weet 't niet heel zeker meer (heb zo'n soort probleem ook ooit gehad), maar volgens mij moest ik toen de puntkomma's in de formule ff veranderen in gewone komma's (klinkt raar, dat weet ik). 't Is het proberen waard nietwaar?

Het valt me trouwens wel op dat je in de 1e macro gebruik maakt van nederlandstalige functies en in de 2e van engelse...

Groet, Leo

 

[script883]

De puntkomma's veranderen in komma's werkt idd. Het klinkt idd raar maar het belangrijkste is dat het werkt. De enieg uitleg die ik ervoor kan verzinnen is dat als je formules manueel invult in excel je de argumenten steeds scheidt door puntkomma en in VBA worden argumenten altijd door komma gescheiden. (Denk ik toch)

Bedankt hiervoor

Ps : reden voor verschil in nederlands en engels is omdat ik eerst op andere (met engelse excel versie) had zitten werken. en dan op laatste eentje opgenomen had op pc met nederlandse excel. Heb uiteindelijk verder gewerkt met de engelse.

Groeten Tom

 

[Finch]

De "gewone" interface van Excel is taalgebonden, maar VBA is dat nooit. Deze volgt altijd de Engelse (lees: Amerikaanse) taal. Dat wil dus zeggen , ipv ; bij formules, en namen van formules altijd in het Engels. Ookal werk je met een NL excel.

 

[Wigi]

En een kommagetal in VBA als 0.01 bvb...

En datumformaat,

en ga zo maar door...