Auftragsprogrammierung

Projektanfrage über Web Formular   per Online-Formular 
Projektanfrage über E-Mail  Diese E-Mail-Adresse ist vor Spambots geschützt! Zur Anzeige muss JavaScript eingeschaltet sein!
Projektanfrage per Telefon  +49 (0)151 / 164 55 914
Projektanfrage weitere Informationen  weitere Informationen 

Sie benötigen Hilfe oder Unterstützung? Nutzen Sie für Ihre Anfrage unser Online-Formular, senden Sie uns eine Diese E-Mail-Adresse ist vor Spambots geschützt! Zur Anzeige muss JavaScript eingeschaltet sein!  oder rufen Sie uns einfach an.

Referenzen

 Bosch 
  T-Systems
  Hagebau
  Siemens
 Areva  VW
 Haufe-Lexware  British American Tobacco
  nagel group farbe

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.



Drucken E-Mail

Relevante Artikel

  • ActiveCell: Fragen und Antworten

    Frage im Spotlight-Forum zum Befehl ActiveCell: Hallo, warum funktioniert denn folgender VBA-Code nicht? SyntaxHighlighter.config.clipboardSwf =...

  • Bilder in Zellen importieren

    Per VBA lassen sich Bilder aus einem angegebenen Verzeichnis auslesen und importieren. Die vorgestellte Prozedur liest die Bilder aus dem...

  • Sortieren mit der BubbleSort Routine

    Das Thema Excel und Sortieren ist sehr vielfältig. In diesem Beitrag zeigen wir Ihnen, wie Zahlen per VBA mit Hilfe der sogenannten BubbleSort...

  • Gesperrte Zellen markieren bzw. einfärben

    Mit dem folgenden Makro können sie alle gesperrten Zellen mit roter Hintergrundfarbe im selektierten Bereich kennzeichnen. {code}Public Sub...

  • Cells- zur Range-Schreibweise umwandeln

    Mit dieser Funktion können Sie die die Cells-Schreibweise, z. B. Cells(1, 2) in die Range-Schreibweise z. B. Range("B1") umwandeln.   {code} Sub...

Projektanfrage

 Sie benötigen eine Auftragsprogrammierung?
 Oder suchen Unterstützung bei der 
 Lösungsfindung?

  Nutzen Sie unser Anfrageformular

  Jetzt anrufen unter 0151 / 164 55 914

 Diese E-Mail-Adresse ist vor Spambots geschützt! Zur Anzeige muss JavaScript eingeschaltet sein! Projektanfrage per Diese E-Mail-Adresse ist vor Spambots geschützt! Zur Anzeige muss JavaScript eingeschaltet sein! senden

  Weitere Informationen

sitepanel question2

P
R
O
J
E
K
T
A
N
F
R
A
G
E
Cookies erleichtern die Bereitstellung unserer Dienste. Mit der Bestätigung erklären Sie sich mit der Verwendung von Cookies auf Excel-Inside Solutions einverstanden.