Mittels VBA dynamische Dropdown-Liste erstellen

Forumthread: Mittels VBA dynamische Dropdown-Liste erstellen

30.07.2019 16:30:32

Michel

Moin moin,
ich benötige für eine Datei einen Code, der folgendes tun soll:
Auf einem Tabellenblatt soll in Zelle C9 mittels eines Makros, das über eine Schaltfläche ausgeführt wird eine Dropdown-Liste eingefügt werden.
Die Daten dazu kommen aus einem anderen Tabellenblatt (gleiche Datei). Die Liste ist aber dynamisch. Die Länge beträgt zwar immer 232 Zeilen, jedoch werden die Einträge automatisch aussortiert, die in der Dropdown-Liste ausgewählt werden und einer anderen Liste mittels Makro hinzugefügt werden. Ein solcher Eintrag verschwindet dann aus der Quellliste und dafür ist am Ende der Liste eine 0, statt des früheren Eintrags (Listenlänge immer noch 232 Zeilen, aber pro ausgewählter Eintrag eine Null am Ende der Liste).
Nun möchte ich mithilfe eines Makros die Dropdown-Liste in Zelle C9 hinzufügen und es sollen mir aus der Quellliste nur die Einträge angezeigt werden, die keine 0 sind. Dies mache ich mithilfe der Formeln "zählenwenn", die die Anzahl der Zeilen zählt, die keine 0 sind und diesen Wert an die Formel "Bereich.verschieben" übergibt, die dann nur die Werte in der Dropdown-Liste anzeigt, die keine 0 sind.
All das funktioniert wie ich will und ich habe dazu ein Makro aufgezeichnet und einer Schaltfläche zugewiesen, damit ich die Formel bei Knopfdruck in Zelle C9 einfügen kann. Jedoch gibt es einen Laufzeitfehler 1004, wenn ich dieses Makro aufrufe.
Kann das Makro etwa die Formel nicht in die Zelle schreiben?
Ist sehr kompliziert zu beschreiben, aber ich hoffe, mir kann jemand helfen und bedanke mich im Voraus.
VG
Michel

1
Beitrag zum Forumthread

Beitrag zu diesem Forumthread

Betreff

Datum

Anwender

AW: Mittels VBA dynamische Dropdown-Liste erstellen

30.07.2019 16:33:45

Werner

AW: Mittels VBA dynamische Dropdown-Liste erstellen

30.07.2019 16:33:45

Werner

Hallo Michel,
wird wohl am Makro liegen - aber das kennst nur du.
Gruß Werner

Infobox / Tutorial

Dynamische Dropdown-Liste in Excel mit VBA erstellen

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Um eine dynamische Dropdown-Liste in Excel zu erstellen, die Daten aus einem anderen Tabellenblatt bezieht, folge diesen Schritten:

Öffne dein Excel-Dokument und gehe zu dem Tabellenblatt, wo du die Dropdown-Liste erstellen möchtest.
Drücke ALT + F11, um den VBA-Editor zu öffnen.
Füge ein neues Modul hinzu:
- Klicke im Menü auf Einfügen > Modul.

Kopiere den folgenden VBA-Code in das Modul:

Sub ErstelleDropdownListe()
   Dim wsQuelle As Worksheet
   Dim wsZiel As Worksheet
   Dim letzteZeile As Long
   Dim i As Long
   Dim rng As Range

   Set wsQuelle = ThisWorkbook.Sheets("Quellblatt") 'Ändere "Quellblatt" zum Namen deines Quellblatts
   Set wsZiel = ThisWorkbook.Sheets("Zielblatt") 'Ändere "Zielblatt" zum Namen deines Zielblatts

   letzteZeile = wsQuelle.Cells(wsQuelle.Rows.Count, "A").End(xlUp).Row
   Set rng = wsZiel.Range("C9")

   'Erstelle die Dropdown-Liste in C9
   With rng.Validation
       .Delete
       .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=BereichVerschieben(wsQuelle, letzteZeile)
       .IgnoreBlank = True
       .InCellDropdown = True
       .ShowInput = True
       .ShowError = True
   End With
End Sub

Function BereichVerschieben(ws As Worksheet, letzteZeile As Long) As String
   Dim i As Long
   Dim liste As String

   For i = 1 To letzteZeile
       If ws.Cells(i, 1).Value <> 0 Then
           liste = liste & ws.Cells(i, 1).Value & ","
       End If
   Next i

   'Entferne das letzte Komma
   If Len(liste) > 0 Then
       liste = Left(liste, Len(liste) - 1)
   End If

   BereichVerschieben = liste
End Function

Passe die Blattnamen an, indem du "Quellblatt" und "Zielblatt" durch die tatsächlichen Namen deiner Blätter ersetzt.
Schließe den VBA-Editor und gehe zurück zu Excel.
Füge eine Schaltfläche hinzu, um das Makro auszuführen:
- Gehe zu Entwicklertools > Einfügen > Schaltfläche (Formsteuerung).
- Zeichne die Schaltfläche auf das Blatt und wähle das Makro ErstelleDropdownListe.
Klicke auf die Schaltfläche, um die Dropdown-Liste in Zelle C9 zu erstellen.

Häufige Fehler und Lösungen

Laufzeitfehler 1004: Dieser Fehler kann auftreten, wenn das Makro versucht, die Validierung in eine nicht vorhandene Zelle zu schreiben. Stelle sicher, dass die Zelle C9 existiert und nicht gesperrt ist.
Dropdown-Liste zeigt keine Werte an: Überprüfe die Funktion BereichVerschieben, um sicherzustellen, dass die Quellwerte korrekt abgerufen werden. Es darf keine 0 in der Quellliste vorhanden sein.

Alternative Methoden

Falls du kein VBA verwenden möchtest, kannst du auch eine dynamische Dropdown-Liste in Excel durch die Verwendung von Formeln und der Datenüberprüfung erstellen:

Erstelle eine Hilfsspalte, die nur die Werte ohne 0 anzeigt.
Verwende die Funktion FILTER, um die gewünschten Werte zu extrahieren.
Setze die Datenüberprüfung auf die Hilfsspalte.

Praktische Beispiele

Angenommen, du hast auf dem „Quellblatt“ eine Liste in Spalte A. Du möchtest eine dynamische Dropdown-Liste in Excel für Zelle C9 auf dem „Zielblatt“ erstellen. Nutze den oben genannten VBA-Code, um die Liste dynamisch zu generieren, und passe den Code entsprechend deiner Datenstruktur an.

Tipps für Profis

Nutze UserForms, um ansprechendere Dropdown-Listen zu erstellen. Mit Excel VBA UserForm Dropdown-Listen kannst du individuelle Steuerelemente und Layouts gestalten.
Optimiere den VBA-Code, indem du Fehlerbehandlungen hinzufügst, um eine bessere Benutzererfahrung zu gewährleisten.

FAQ: Häufige Fragen

1. Wie kann ich die Dropdown-Liste auf ein anderes Tabellenblatt verweisen?
Du kannst den Namen des Quellblatts im VBA-Code anpassen, indem du die Zeile Set wsQuelle = ThisWorkbook.Sheets("Quellblatt") entsprechend änderst.

2. Funktioniert das auch in älteren Excel-Versionen?
Der Code ist für Excel 2010 und neuere Versionen geeignet. Ältere Versionen unterstützen möglicherweise nicht alle Funktionen.

Beliebteste Forumthreads (12 Monate)

Mittels VBA dynamische Dropdown-Liste erstellen

Forumthread: Mittels VBA dynamische Dropdown-Liste erstellen

1 Beitrag zum Forumthread Beitrag zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

Dynamische Dropdown-Liste in Excel mit VBA erstellen

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

1
Beitrag zum Forumthread

Beitrag zu diesem Forumthread