Dynamische DropDown-Liste mit Text Vorauswahl

In diesem Beitrage zeigen wir, wie sich die Inhalte von DropDown-Felder dynamisch erzeugen lassen.

Die Beispieldatei ist wie folgt aufgebaut:

Im Zellbereich B6:B16 sind alle Begriffe eingetragen, welche in der DropDown-Box in Zelle F6 zur Auswahl stehen sollen. Dieser Zellbereich enthält die Gesamtmenge aller Auswahlbegriffe, welche aber bei Bedarf noch weiter eingeschränkt werden sollen. Die Einschränkung erfolgt über die Eingabe in Zelle C3. Sollen für das DropDown-Menü bspw. nur alle Einträge angezeigt werden, die die Ziffer 1 enthalten, dann muss nichts weiter gemacht werden, also in die Zelle C3 die Ziffer 1 einzutragen.

VBA-Code

Im Hintergrund läuft automatisch die VBA-Prozedur ab, welche dafür sorgt, dass alle Einträge aus Spalte B in ab Zelle C6 aufgelistet werden, welche die Ziffer 1 im Text beinhalten.

Die VBA-Prozedur durchsucht dazu alle Einträge ab Zelle B6 nach der Ziffer 1. Sobald der Suchbegriff gefunden wurde, wird dieser Treffer entsprechend in Spalte C ab Zelle C6 eingetragen, siehe Abbildung 1.

dropdown vorauswahl 1
Abbildung 1



Das folgende VBA Makro muss in dem Code-Modul des betreffenden Blatts abgelegt werden, da das Worksheet Change-Ereignis ausgeführt werden muss, sobald der Eintrag in Zelle C3 geändert wird.

Private Sub Worksheet_Change(ByVal Target As Range)
'** Prüfen, ob der Wert in Zelle C2 geändert wurde
If Not Application.Intersect(Target, Range("C3")) Is Nothing Then
 
  '** Screenupdating und Berechnung deaktivieren
  With Application
    .ScreenUpdating = False
   .Calculation = xlCalculationManual
  End With
 
  '** Dimensinionierung der Variablen
  Dim wsDat
  Dim lngZeile As Long
  Dim lngPos As Long
 
  '** Vorgaben definieren
  Set wsDat = ThisWorkbook.Sheets(1)
  lngZeile = 6 '** Startzeile für Ausgabe in Spalte C
 
  '** Ausgabebereich löschen
  wsDat.Range("C6:C100").ClearContents
 
  '** Spalte B ab Zeile 6 bis zur letzten gefüllten Zeile durchlaufen
  For a = 6 To wsDat.Cells(Rows.Count, 2).End(xlUp).Row
   
    '** Ermittlung der Position, ab der das gesuchte Zeichen aus Zelle C3 gefunden wurde
    lngPos = InStr(1, LCase(wsDat.Cells(a, 2).Value), LCase(wsDat.Range("C3").Value))
   
    '** Daten in Spalte C auflisten, wenn der Suchbegriff vorhanden ist
    If lngPos > 0 Then
      wsDat.Cells(lngZeile, 3).Value = wsDat.Cells(a, 2).Value
     
      '** Zeilenzähler erhöhen
      lngZeile = lngZeile + 1
    End If
   
  Next a
  '**Berechnung und Bildschirmaktualisierung wieder einschalten
  With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
  End With
End If
End Sub

Nachdem das Makro alle Zeilen durchlaufen hat, werden nun alle Begriffe, welche die 1 enthalten ab Zelle C6 aufgelistet.

Bereichsnamen definieren

Im nächsten Schritt muss nun nur noch der DropDown-Liste in Zelle F6 die neue Liste hinterlegt werden, damit eben nur die Begriffe aus Spalte C, beginnend mit Zeile 6 in der DropDown-Box zur Auswahl stehen.

Dies wird mit einem Bereichsnamen erledigt.

Starten Sie dazu den Namensmanager unter Formeln / Definierte Namen / Namensmanager und erfassen den neuen Namen n_bereich. Im Feld Bezieht sich auf hinterlegen Sie folgende Formel, siehe Abbildung 2:

=BEREICH.VERSCHIEBEN (DropDown!$C$6; 0;0; ANZAHL2(DropDown!$C$6:$C$100);1)

dropdown vorauswahl 2
Abbildung 2

Mit Hilfe der Funktion BEREICH.VERSCHIEBEN() in Verbindung mit ANZAHL2() werden alle Begriffe ab Zelle C6 bis zur letzten vorhanden Zeile, im Beispiel bis zur Zeile 10 erkannt und dem Namen n_bereich übergeben.

Datenüberprüfung / Gültigkeitsprüfung erstellen

Damit nun der definierte Bereich mit den selektierten Begriffen aus den Zellen C6:C10 als DropDown-Auswahl zur Verfügung steht, muss dies mit Hilfe der Funktion Datenüberprüfung in Zelle F6 hinterlegt werden.

Starten Sie dazu das Dialogfenster Datenüberprüfung über den Befehl Daten / Datentools / Datenüberprüfung.

Wählen Sie im Feld Zulassen den Eintrag Liste und unter Quelle erfassen Sie den definierten Bereichsnamen =n_bereich, welchem die dynamischen Werte aus Spalte C übergeben wurden, siehe Abbildung 3.

dropdown vorauswahl 3
Abbildung 3

Ergebnis

Die Datei ist nun fertig und funktioniert wunschgemäß. Wenn das DropDown-Menü in Zelle F6 geöffnet wird, dann werden nur die Spalte C dynamisch erzeugten Begriffe angezeigt und es kann aus dieser Liste ein beliebiger Eintrag ausgewählt werden, siehe Screenshot 4.

dropdown vorauswahl 4
Abbildung 4

Die Beispieldatei können Sie über den nachfolgenden Link herunterladen und Ihren Bedürfnissen entsprechend anpassen.



Partnerlinks

Relevante Artikel

  • Zeilen (Einträge) abhängig vom Datum löschen

    Aus diesem Datenbestand sollen die Zeilen gelöscht werden, deren Datum in Spalte A im Monat November liegt.     A B C D 1 12.08.2002 daten daten daten 2 15.08.2002 daten daten daten 3 20.08.2002 daten daten daten 4 12.09.2002 daten daten daten 5 15.09.2002 daten daten daten 6 20.09.2002 daten daten daten 7 12.10.2002 daten daten daten 8 15.10.2002 daten daten daten 9 20.10.2002 daten daten daten 10 15.11.2002 daten daten daten 11 15.11.2002 daten daten daten 12 15.11.2002 daten daten daten 13 15.11.2002 daten daten daten 14 12.12.2002 daten daten daten 15 15.12.2002 daten daten daten 16 20.12.2002 daten daten daten Nach...

  • Verbundene Zellen finden

    Das folgende Makro findet alle verbunden Zellen und gibt die Zelladressen in einer MessageBox aus.{code}Sub VerbundeneZellen_Adressen()'Die...

  • ActiveCell: Fragen und Antworten

    Frage im Spotlight-Forum zum Befehl ActiveCell: Hallo, warum funktioniert denn folgender VBA-Code nicht? {code} If...

  • Zell-Selektion auf nicht geschützte Zellen beschränken

    Dieses Makro beschränkt die Zell-Auswahl auf ungeschützte Zellen. Dazu ist es notwendig, dass der Blattschutz gesetzt ist....

  • Konvertieren eines mehrzeiligen Daten-Blocks zu einem konstistenten Datensatz

    In diesem Beispiel zeigen wir Ihnen, wie sich ein nicht konsistenter Datenblock in einen kosistenten Datensatz konvertieren lässt. Die...

Ihre Zufriedenheit ist unser Ziel, deshalb verwenden wir Cookies. Mit diesen ermöglichen wir, dass unsere Webseite zuverlässig und sicher läuft, wir die Performance im Blick behalten und Sie besser ansprechen können. Cookies werden benötigt, damit technisch alles funktioniert und Sie auch externe Inhalte lesen können. Des Weiteren sammeln wir unter anderem Daten über aufgerufene Seiten, getätigte Käufe oder geklickte Buttons, um so unser Angebot an Sie zu Verbessern. Mehr über unsere verwendeten Dienste erfahren Sie unter „Weitere Informationen“. Mit Klick auf „Akzeptieren“ erklären Sie sich mit der Verwendung dieser Dienste einverstanden. Ihre Einwilligung können Sie jederzeit mit Wirkung auf die Zukunft widerrufen oder ändern.