Data Validatie toevoegen aan een cel via VBA

Data validatie in Excel zorgt ervoor dat gebruikers alleen correcte en verwachte invoer kunnen doen in een cel. Met VBA (Visual Basic for Applications) kun je dit proces automatiseren en uitbreiden met extra functionaliteiten. In dit artikel bespreken we hoe je data validatie toevoegt via VBA en hoe je problemen met decimale scheidingstekens in lijsten kunt oplossen.

Waarom Data Validatie via VBA?

Hoewel Excel ingebouwde data validatie biedt via het lint, heeft VBA enkele voordelen:

• Je kunt validatieregels automatisch toepassen op meerdere cellen tegelijk.

• Je kunt validatie combineren met andere VBA-functionaliteiten, zoals invoercontroles of foutmeldingen.

• Je kunt dynamische lijsten of aangepaste formules gebruiken voor validatie.

Basisvoorbeeld: Een Getal tussen 1 en 100

Hieronder staat een eenvoudig voorbeeld waarin we een cel valideren om alleen getallen tussen 1 en 100 toe te staan.

Sub VoegDataValidatieToe()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Blad1")
    
    With ws.Range("A1").Validation
        .Delete ' Verwijder bestaande validatie
        .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="1", Formula2:="100"
        .IgnoreBlank = True
        .ShowInput = True
        .ShowError = True
        .InputTitle = "Invoer vereist"
        .ErrorTitle = "Ongeldige invoer"
        .InputMessage = "Voer een getal in tussen 1 en 100."
        .ErrorMessage = "De invoer moet tussen 1 en 100 liggen."
    End With
End Sub

Dit script zorgt ervoor dat cel A1 alleen waarden tussen 1 en 100 accepteert. Voert iemand een ongeldige waarde in, dan verschijnt er een foutmelding.

 

Datumvalidatie: Alleen Geldige Datums Toestaan

Bij het werken met datums is het belangrijk dat gebruikers alleen datums invoeren die binnen een bepaald bereik vallen. Dit voorkomt foutieve invoer, zoals een datum uit het verleden voor een toekomstige afspraak.

Hier is een VBA-script dat alleen datums toestaat binnen de komende 30 dagen:

 

 

Sub VoegDatumValidatieToe()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Blad1")
    
    With ws.Range("D1").Validation
        .Delete
        .Add Type:=xlValidateDate, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
             Formula1:="=TODAY()", Formula2:="=TODAY()+30"
        .IgnoreBlank = True
        .ShowInput = True
        .ShowError = True
        .InputTitle = "Voer een geldige datum in"
        .ErrorTitle = "Ongeldige datum"
        .InputMessage = "Voer een datum in binnen de komende 30 dagen."
        .ErrorMessage = "De datum moet tussen vandaag en 30 dagen in de toekomst liggen."
    End With
End Sub

Dit zorgt ervoor dat alleen datums tussen vandaag en 30 dagen vooruit ingevoerd kunnen worden. Voert een gebruiker een datum buiten dit bereik in, dan krijgt hij een foutmelding.

Praktische toepassingen van datumvalidatie

• Beperken van invoer voor reserveringssystemen tot een bepaalde periode.

• Voorkomen dat oude of toekomstige factuurdata worden ingevoerd.

• Beperken van projectdeadlines tot een vastgestelde tijdsperiode.

Door datumvalidatie via VBA toe te passen, kun je de betrouwbaarheid van datumgegevens in je werkbladen verbeteren en invoerfouten minimaliseren.

Tekstlengte-validatie: Beperken van Aantal Karakters

In sommige gevallen is het nodig om de lengte van een tekstinvoer te beperken, bijvoorbeeld voor postcodes, telefoonnummers of klantnummers. Met VBA kun je eenvoudig een minimum- en maximumlengte instellen.

Hier is een VBA-script dat alleen tekst tussen 5 en 10 karakters toestaat:

Sub VoegTekstlengteValidatieToe()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Blad1")
    
    With ws.Range("E1").Validation
        .Delete
        .Add Type:=xlValidateTextLength, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
             Formula1:="5", Formula2:="10"
        .IgnoreBlank = True
        .ShowInput = True
        .ShowError = True
        .InputTitle = "Voer een geldige tekst in"
        .ErrorTitle = "Ongeldige invoer"
        .InputMessage = "Voer een tekst in van 5 tot 10 karakters."
        .ErrorMessage = "De tekst moet tussen de 5 en 10 karakters lang zijn."
    End With
End Sub

Hierdoor kan de gebruiker alleen tekst invoeren met een lengte tussen de 5 en 10 karakters. Dit is handig voor velden zoals postcodes, wachtwoorden of andere gestructureerde gegevens.

Praktische toepassingen van tekstlengte-validatie

• Beperken van klantnummers tot een vast aantal tekens.

• Voorkomen van te korte of te lange wachtwoorden.

• Afdwingen van correcte postcodeformaten.

Met deze methode zorg je ervoor dat gebruikers de juiste lengte aanhouden voor belangrijke tekstvelden, wat invoerfouten en inconsistenties voorkomt.

Aangepaste formules: Geavanceerde Validatie

Soms heb je specifieke validatieregels die niet standaard in Excel beschikbaar zijn. In dat geval kun je aangepaste formules gebruiken om invoer te beperken. Een voorbeeld hiervan is het afdwingen dat een cel altijd een even getal bevat.

Hier is een VBA-script dat alleen even getallen toestaat:

Sub VoegAangepasteValidatieToe()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Blad1")
    
    With ws.Range("F1").Validation
        .Delete
        .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Formula1:="=MOD(F1,2)=0"
        .IgnoreBlank = True
        .ShowInput = True
        .ShowError = True
        .InputTitle = "Voer een even getal in"
        .ErrorTitle = "Ongeldige invoer"
        .InputMessage = "De invoer moet een even getal zijn."
        .ErrorMessage = "Voer een getal in dat deelbaar is door 2."
    End With
End Sub

Hiermee wordt gecontroleerd of het ingevoerde getal deelbaar is door 2. Als een oneven getal wordt ingevoerd, verschijnt er een foutmelding.

Praktische toepassingen van aangepaste formules

• E-mailadres validatie: Controleer of een invoer een ‘@’ bevat met =ISNUMBER(SEARCH("@", A1)).

• Specifiek tekstpatroon: Sta alleen codes toe die beginnen met ‘ABC’ met =LEFT(A1,3)="ABC".

• Combinaties van voorwaarden: Sta alleen positieve even getallen toe met =AND(A1>0, MOD(A1,2)=0).

Door aangepaste formules te gebruiken, kun je zeer specifieke invoerbeperkingen afdwingen die met standaard validatie-opties niet mogelijk zijn.

Een Lijst als Validatiebron

Soms wil je een lijst met opties aanbieden. Dit kan met het volgende VBA-script:

Sub VoegLijstValidatieToe()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Blad1")
    
    With ws.Range("B1").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="Optie1,Optie2,Optie3"
        .IgnoreBlank = True
        .ShowInput = True
        .ShowError = True
        .InputTitle = "Kies een optie"
        .ErrorTitle = "Ongeldige invoer"
        .InputMessage = "Selecteer een optie uit de lijst."
        .ErrorMessage = "De waarde moet een van de opgegeven opties zijn."
    End With
End Sub

In cel B1 kan een gebruiker nu enkel kiezen uit ‘Optie1’, ‘Optie2’ en ‘Optie3’.

 

Probleem: Decimaal Scheidingsteken en Lijsten

Een veelvoorkomend probleem bij lijstvalidatie is het gebruik van komma’s als scheidingsteken. In sommige landen, zoals Nederland, wordt de komma als decimaal scheidingsteken gebruikt (bijvoorbeeld 12,50). Echter, in een lijstvalidatie worden komma’s als scheidingsteken tussen items gezien. Hierdoor wordt een bedrag zoals “12,50” als twee aparte invoerwaarden (“12” en “50”) geïnterpreteerd.

Oplossing: Gebruik Chr(130) als Alternatief voor de Komma

Een handige manier om dit probleem te omzeilen is door in je VBA-code de komma te vervangen door Chr(130), wat in Excel hetzelfde wordt weergegeven als een komma, maar niet als lijstscheidingsteken wordt behandeld.

Sub VoegBedragValidatieToe()
    Dim ws As Worksheet
    Dim bedrag As String
    
    Set ws = ThisWorkbook.Sheets("Blad1")
    
    ' Voorbeeld bedrag met een komma
    bedrag = "12,50"
    
    ' Vervang de komma door Chr(130)
    bedrag = Replace(bedrag, ",", Chr(130))
    
    With ws.Range("C1").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=bedrag
        .IgnoreBlank = True
        .ShowInput = True
        .ShowError = True
        .InputTitle = "Voer een bedrag in"
        .ErrorTitle = "Ongeldige invoer"
        .InputMessage = "Voer een bedrag in het juiste formaat in."
        .ErrorMessage = "Gebruik een geldig bedrag met twee decimalen."
    End With
End Sub

Hierdoor blijft de weergave van de komma correct, maar wordt deze niet meer als scheidingsteken gezien binnen de lijstvalidatie. Indien je vervolgens met deze bedragen wilt rekenen, dien je natuurlijk de komma weer terug te plaatsen, zodat het bedrag niet als tekst wordt gezien.