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, cl As Range, bDuplicate As Boolean
    If Not Intersect(Target, Range("no_duplicates")) Is Nothing Then
        Set rng = Intersect(Target, Range("no_duplicates"))
        bUndo = False
        If rng.Cells.Count > 1 Then
            For Each cl In rng.Cells
                If Application.CountIf(Range("no_duplicates"), cl.Value) > 1 Then
                    bDuplicate = True
                End If
            Next
            If bDuplicate Then
                MsgBox "Eén of meer van de ingegeven waarden bestaat al en dubbele waarden zijn niet toegestaan!", vbCritical, "Dubbele waarde"
                bUndo = True
            End If
        ElseIf Application.CountIf(Range("no_duplicates"), 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

Dit stukje code checkt ook op dubbele waarden indien een gebruiker in één keer meerdere waarden tegelijk wil toevoegen.

Vragen / suggesties

Hopelijk heeft dit artikel geholpen bij het voorkomen van het invoeren van dubbele waarden. Als er verdere vragen over dit onderwerp zijn of suggesties voor verbetering, plaats dan een reactie hieronder.

arrow_up