Vba Dynamische validatie lijst

[Magoo]

Heb een probleem met gegevensvalidatie->lijst

Als ik bij excel-sheet gegevensvalidatie-> lijst de volgende formule gebruik: =VERSCHUIVING(Diensten!$A$2;0;0;AANTALARG(Diensten!A:A)) gaat alles goed.
Ik heb in de sheet Diensten in kolom A gegevens staan die ik via een dynamische gegevensvalidatie->Lijst wil gebruiken.
maar als ik deze via een VBA een subroutine laat uitvoeren geeft deze de volgende foutcode 1004 (door de toepassing of door Object gedefineerde fout)

Sub ValidatieLijst()
  Range("G7").Select
  With Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=VERSCHUIVING(diensten!$A$2;0;0;AANTALARG(diensten!A:A))"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
  End With
End Sub

Wie weet de oplossing om dynamische validatielijst te maken via vba
Ik weet dat je via een functie een samensgesteld bereik kunt samenstellen door de laatste rij in de kolom te detecteren en deze te gebruiken.
Maar dan is de validatie lijst niet meer dynamisch, als je naderhand gegevens bijvoegt in de lijst.

 

[JeanPaul28]

Maak er een naam bereik van en zet er jou formule in "=VERSCHUIVING(Diensten!$A$2;0;0;AANTALARG(Diensten!A:A))" dan is die dynamisch en in de validatie zet je bij lijst de naam van je naam bereik.

 

[VenA]

Ik zou een tabel gebruiken.

Nb. VBA is in het Engels VERSCHUIVING() lijkt mij Nederlands.

 

[Magoo]

Ik zou een tabel gebruiken.

Nb. VBA is in het Engels VERSCHUIVING() lijkt mij Nederlands.

Dat klopt, als ik alleen verschuiving gebruik in de formule werkt het wel, maar heb ik niet de aantal ingevulde velden
Had de macro ook opgenomen met de macrorecorder
Als ik OFFSET gebruik werkt het ook niet.
Ga proberen een naambereik ervan te maken

 

[Magoo]

Maak er een naam bereik van en zet er jou formule in "=VERSCHUIVING(Diensten!$A$2;0;0;AANTALARG(Diensten!A:A))" dan is die dynamisch en in de validatie zet je bij lijst de naam van je naam bereik.

Ik heb er een Naambereik van gemaakt zoals je schreef, en dat werkt wel :thumb:
Alleen raar als je het handmatig invult, dat het wel werkt, en via vba niet.
Maar probleem is opgelost, Dank je

P/s Naambereik maakt er "=OFFSET(Diensten!$A$2,0,0,COUNTA(Diensten!$A:$A))" van
Nog sterker als ik deze code gebruik in de gegevensvalidatie werkt het wel
Dus de marco recorder maakt een behoorlijk foutje!

 

[edmoor]

De macro recorder neemt exact op wat je zelf ingeeft.

 

[snb]

Vermijd 'Select' en 'Activate' in VBA
VBA heeft meer te bieden dan de macrorecorder toont:
Maak van kolom A een dynamische tabel met in bijv. A1 'dienst'

Sub M_snb()
   [Table1[dienst]].Name = "snb"
   Cells(7, 7).Validation.Modify , , , "=snb"
End Sub

 

[Magoo]

De macro recorder neemt exact op wat je zelf ingeeft.

Hier ben ik het niet mee eens!

Normaal worden de Nederlandse functie's omgezet naar Engelse benamingen.
Gebruik weinig tot niet de macrorecorder, en aangezien de formule als string werd weergeven, dacht ik dat het goed was, heb me dit keer laten misleiden.
En aangezien Index in NL en Engels gelijk is heeft ook bijgedragen aan mijn vertroebeld beeld

Maar je zou ook kunnen constateren dat er een foutje zit in de macrorecorder betreffende gegevens validatie.
Hier worden de functie's niet omgezet naar de Engelse benaming, normaal wordt dat wel gedaan.

 

[edmoor]

Je kan het er mee eens zijn of niet.
Het is niet anders.

 

[Magoo]

Vermijd 'Select' en 'Activate' in VBA
VBA heeft meer te bieden dan de macrorecorder toont:
Maak van kolom A een dynamische tabel met in bijv. A1 'dienst'

Sub M_snb()
   [Table1[dienst]].Name = "snb"
   Cells(7, 7).Validation.Modify , , , "=snb"
End Sub

Hoi snb,

long time no see

Ik weet dat je geen onnodige code moet maken, maar was de code van de macrorecorder.
In uitzonderlijke gevallen gebruik ik de indirecte code wel eens, maar is zeldzaam, gebruik liever de directe code.

P/s
En..........
Ben niet meer zo actief bezig met VBA, en de leeftijd......

 

[snb]

Hola @Magoo

Ik zond je een PM.