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.


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)

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.

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.


Abbildung 4

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