Tutorials

Tutorial: Excel VBA zur Erstellung von Datenüberprüfungen und Dropdown-Listen

Melden

Hier ist ein ausführliches Tutorial, wie du mit Excel VBA eine Datenüberprüfung (Data Validation) inklusive einer Dropdown-Liste erstellst.

Die Datenüberprüfung in Excel ist ein mächtiges Werkzeug, um die Dateneingabe zu standardisieren. Mit VBA kannst du diesen Prozess automatisieren, was besonders nützlich ist, wenn du dynamische Formulare oder Berichte erstellst.

1. Vorbereitungen

Bevor du startest, stelle sicher, dass die Registerkarte Entwicklertools in deinem Excel Menüband sichtbar ist.

  1. Drücke ALT + F11, um den VBA-Editor zu öffnen.
  2. Gehe auf Einfügen > Modul, um ein neues Modul zu erstellen.

2. Der grundlegende VBA-Code

Hier ist ein Standard-Code-Block, der in einer Zelle (z. B. A1) eine Dropdown-Liste mit fest vorgegebenen Werten erstellt.

Sub ErstelleEinfachesDropdown()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Tabelle1") ' Name deiner Tabelle anpassen

    Dim zielZelle As Range
    Set zielZelle = ws.Range("A1")

    ' Zuerst bestehende Validierungen löschen, um Fehler zu vermeiden
    With zielZelle.Validation
        .Delete
        ' Neue Validierung hinzufügen
        .Add Type:=xlValidateList, _
             AlertStyle:=xlValidAlertStop, _
             Operator:=xlBetween, _
             Formula1:="Option A, Option B, Option C" ' Hier die Werte trennen

        ' Weitere Einstellungen
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = "Bitte wählen"
        .ErrorTitle = "Ungültige Eingabe"
        .InputMessage = "Wähle einen Wert aus der Liste aus."
        .ErrorMessage = "Dieser Wert ist nicht erlaubt!"
        .ShowInput = True
        .ShowError = True
    End With
End Sub

3. Die Parameter erklärt

  • .Delete: Es ist wichtig, zuerst .Delete aufzurufen, da Excel eine Fehlermeldung ausgibt, wenn du versuchst, .Add auf eine Zelle anzuwenden, die bereits eine Datenüberprüfung hat.
  • Type:=xlValidateList: Legt fest, dass es sich um eine Dropdown-Liste handelt.
  • Formula1: Hier gibst du die Optionen an. Wichtig: In VBA werden die Optionen oft durch ein Komma getrennt, unabhängig von deinen regionalen Windows-Einstellungen (die in Excel-Formeln oft ein Semikolon verlangen).
  • InCellDropdown:=True: Erzeugt den kleinen Pfeil neben der Zelle.

4. Dynamische Liste aus einem Zellbereich

Oft liegen die Daten für das Dropdown bereits in einem Bereich in deiner Excel-Tabelle (z. B. Spalte E). So referenzierst du einen Bereich:

Sub DropdownAusBereich()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Tabelle1")

    ' Der Bereich, der die Optionen enthält
    Dim listenBereich As String
    listenBereich = "=$E$1:$E$10"

    With ws.Range("B2").Validation
        .Delete
        .Add Type:=xlValidateList, _
             AlertStyle:=xlValidAlertStop, _
             Formula1:=listenBereich
        .InCellDropdown = True
    End With
End Sub

5. Profi-Tipp: Dynamische letzte Zeile finden

Wenn deine Liste in Spalte E ständig wächst, kannst du VBA nutzen, um automatisch alle Einträge zu erfassen:

Sub DynamischesDropdownLetzteZeile()
    Dim ws As Worksheet
    Dim letzteZeile As Long
    Set ws = ThisWorkbook.Sheets("Tabelle1")

    ' Findet die letzte gefüllte Zeile in Spalte E
    letzteZeile = ws.Cells(ws.Rows.Count, "E").End(xlUp).Row

    With ws.Range("C2").Validation
        .Delete
        ' Bereich dynamisch von E1 bis zur letzten Zeile setzen
        .Add Type:=xlValidateList, _
             Formula1:="=$E$1:$E$" & letzteZeile
        .InCellDropdown = True
    End With
End Sub

6. Fehlerbehebung & Hinweise

  1. Laufzeitfehler 1004: Dieser tritt meist auf, wenn du versuchst, eine Validierung hinzuzufügen, ohne die alte vorher mit .Delete zu löschen.
  2. Blattschutz: Wenn das Arbeitsblatt geschützt ist, kann VBA die Datenüberprüfung nicht ändern. Du musst den Schutz vorher mit ws.Unprotect "Passwort" aufheben und danach mit ws.Protect "Passwort" wieder setzen.
  3. Namensbereiche: Du kannst in Formula1 auch einen benannten Bereich angeben (z. B. Formula1:="=MeinListenName").

Zusammenfassung

Mit dem Validation-Objekt in VBA hast du volle Kontrolle über die Dateneingabe. Denke immer an die Reihenfolge: Zelle bestimmen -> Löschen -> Hinzufügen. Damit verhinderst du die meisten Fehler.

0