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:
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:
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:
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.