wijzig taal:

In gevallen waarin het noodzakelijk is dat een gebruiker in Excel unieke waarden invoert en er dus geen dubbele waarden mogen voorkomen, kan dat afgedwongen worden met behulp van gegevensvalidatie. Stel kolom A bevat de volgende gegevens:

dubbele waarden verhinderen 1

In dit geval is vooraf ingesteld dat in A2:A100 geen dubbele waarden mogen worden ingevoerd. Dit is als volgt gedaan. Zorg dat de invoerkolom leeg is. Selecteer vervolgens vanuit A2 de cellen A2:A100 en kies voor de optie Gegevensvalidatie (deze optie is op het lint bij de tab Gegevens te vinden, maar dat kan per Excel-versie verschillen). Kies vervolgens in het scherm van de gegevensvalidatie bij het tabblad Instellingen bij 'Toestaan:' voor de optie Aangepast:

dubbele waarden verhinderen 2

Bij 'Formule:' dient dan dan de volgende formule te worden ingegeven:

=AANTAL.ALS(A$2:A$100;A2)=1

In dit geval wordt de invoer gecontroleerd op dubbele waarden voor A2 tot en met A100. Dit kan uiteraard aangepast worden. Eventueel kan op het tabblad Foutmelding een specifieke fouttekst ingegeven worden die verschijnt als de gebruiker een dubbele waarde invoert. Kies daarna voor OK en daarmee is de controle op het invoer van dubbele waarden ingesteld voor het celbereik A2:A100.

Als nu bijvoorbeeld in bovenstaand voorbeeld in cel A11 de waarde 5 ingevoerd wordt, dan is dat niet mogelijk en verschijnt onderstaande foutmelding:

dubbele waarden verhinderen 3

Deze melding verschijnt ook als wordt geprobeerd om een al ingevoerde waarde te wijzigen in een waarde die al voorkomt.

Deze oplossing werkt in de meeste gevallen prima maar gegevensvalidatie heeft wel wat beperkingen. Met bijvoorbeeld 'kopiƫren - plakken' of Ctrl+D zou je alsnog dubbele waarden kunnen invoeren zonder dat er een foutmelding komt. Met VBA zou je dit nog wel verder kunnen dichttimmeren door Ctrl+D voor het werkboek uit te schakelen en het kopiƫren plakken te verhinderen voor betreffende celbereik. Maar dan is het beter om een volledige VBA aanpak te kiezen.

Geef daarvoor eerst celbereik A2:A100 de naam 'geen_dubbelen' en zet vervolgens onderstaande code in de Worksheet_Change gebeurtenis van het betreffende werkblad:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range, bUndo As Boolean
    If Not Intersect(Target, Range("geen_dubbelen")) Is Nothing Then
        Set rng = Intersect(Target, Range("geen_dubbelen"))
        bUndo = False
        If rng.Cells.Count > 1 Then
            MsgBox "Het is niet toegestaan om meerdere cellen tegelijk te wijzigen!", vbCritical, "Meerdere cellen gewijzigd"
            bUndo = True
        ElseIf Application.CountIf(Range("geen_dubbelen"), rng.Value) > 1 Then
            MsgBox "De ingevoerde waarde bestaat al en dubbele waarden zijn niet toegestaan!", vbCritical, "Dubbele waarde"
            bUndo = True
        End If
        If bUndo Then
            Application.EnableEvents = False
            Application.Undo
            Application.EnableEvents = True
        End If
    End If
End Sub

In dit stukje code wordt ook verhinderd dat de gebruiker in betreffende bereik meerdere cellen tegelijk wijzigt.

Plaats reactie
Door het plaatsen van een commentaar verklaart u bekend te zijn en akkoord te gaan met de privacy verklaring van worksheetsvba.com.


arrow_up