Manchmal reicht ein einfaches Dropdown-Feld nicht, um zuverlässig den richtigen Wert auszuwählen. Nämlich dann, wenn z.B. in einer Namensliste Einträge mehrfach vorkommen. Welcher ist dann der Richtige?
Jetzt kommt er schon wieder mit seinen Dropdown-Listen daher!, wirst du vielleicht denken. Ich gebe zu, man könnte fast meinen, dass dies nach den beiden Artikeln hier und hier mein neues Steckenpferd ist. Nicht ganz.
Aber wer sich schon immer mal gefragt hat, wie man in einem Dropdown-Feld eine mehrspaltige Liste angezeigt bekommt, sollte sich diesen Artikel doch ansehen:
Und so geht’s:
Kombinationsfeld
Zur Veranschaulichung habe ich ein einfaches Auftragsformular erstellt, in das ich mit Hilfe meiner Dropdown-Liste die Kundenadresse übernehmen möchte:
Auftragsformular
In einem zweiten Arbeitsblatt befindet sich die Liste meiner Kunden. Damit ich den Tabellenbereich später einfacher ansprechen kann, habe ich für den Bereich den Namen „Kundenliste“ vergeben:
Kundenliste
Nun füge ich über die Schaltfläche „Einfügen“ aus den Entwicklertools meine Dropdown-Liste im Namensfeld meines Auftragsformulars ein. Dabei handelt es sich um das Kombinationsfeld aus der Gruppe „ActiveX-Steuerelemente“:
Kombinationsfeld
Nachdem ich es mit der Maus auf die gewünschte Größe angepasst habe, rufe ich über einen Rechtsklick auf das neue Feld das Eigenschaften-Menü auf:
Eigenschaften anpassen
In der langen Liste der möglichen Eigenschaften interessieren uns momentan nur die folgenden fünf:
Relevante Eigenschaften
- ColumnCount: Meine Kundenliste hat 5 Spalten, daher steht hier die „5“
- ColumnWidths: Für jede Spalte definiere ich die gewünschte Spaltenbreite. Da sich in der ersten Spalte die Kundennummer befindet, die mich im Auswahlfeld nicht interessiert, setze ich diese Breite auf 0 (Null).
- LinkedCell: Das ist das Feld, welches mein gewähltes Ergebnis enthalten soll. Da die Kundennummer das erste Feld in meiner Auswahlliste ist (auch wenn es dort nicht angezeigt wird), wird damit im Feld F2 die Kundennummer gespeichert.
- ListFillRange: Hier steht der Name, den ich vorhin für meine Kundenliste vergeben haben
- ListWidth: Das ist Gesamtbreite der Auswahlliste, also der Summe aller Spaltenbreiten
Das war’s auch schon. Wir können das Eigenschaftenfenster wieder schließen und müssen nur noch den Entwurfsmodus wieder abschalten, damit wir unsere Dropdown-Liste testen können:
Entwurfsmodus abschalten
Und so sieht die mehrspaltige Dropdown-Liste in der Anwendung aus. Die Kundennummer des gewählten Eintrags landet wie gewünscht in Zelle F2:
Mehrspaltige Dropdown-Liste
Zu guter letzt hole ich mir über drei kleine SVERWEIS-Funktionen über die Kundennummer die restlichen Adressangaben in die entsprechenden Felder:
Mit SVERWEIS die Adresse holen
Fertig ist mein Auftragsformular.
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Ich finde den Artikel ‚Die Qual der Wahl: Mehrspaltige Dropdown-Listen‘ sehr spannend. Wenn ich aber die Tabelle, in welcher die Quelldaten liegen, erweitern will, habe ich Probleme.
Ich hatte den Bereich als ‚Tabelle‘ formatiert und dieser Tabelle den Namen Kundenliste gegeben – das DropDown beinhaltete auch alle zu diesem Zeitpunkt in der Tabelle befindlichen Datensätze. Eine neu hinzugefügte Adresse wurde allerdings nicht übernommen…. Warum?
Hallo Oliver,
die Frage ist, ob Du in dem Dropdown einen absoluten Zellenbezug verwendest, oder den Namen der Tabelle (also Kundenliste).
Grüße,
Martin
Hallo Martin,
ich bin auf genau die gleiche Frage gestoßen wie Oliver.
Ich habe als ListFillRange den sich dynamisch vergrößernden Bereichsnamen Kundenliste. Wenn ich einen neuen Kunden in die Liste eintrage wird der Bereich Kundenliste automatisch vergrößert, was ich mit F5 überprüfen kann. Aber in der ComboBox kommt die Änderung erst nach einem Schließen und Neu-Öffnen der Arbeitsmappe an.
Any ideas?
Gruß
Matthias
Okay, scheint wohl keine triviale Angelegenheit zu sein, siehe auch:
http://stackoverflow.com/questions/4200712/dynamically-set-listfillrange-in-excel-combobox-using-vba
und
http://stackoverflow.com/questions/1263394/excel-combobox-listfillrange-property-pointing-at-a-formula-based-named-range-ha
Hallo Matthias,
du hast Recht, mir ist dieses Verhalten bisher überhaupt noch nicht aufgefallen. So wie es aussieht, werden die neuen Werte in der Combobox erst verfügbar, wenn man die Excel-Datei speichert, schließt und wieder neu öffnet.
Nicht gerade sehr praktisch und auch nicht das, was man bei dynamischen Bereichen erwarten würde…
Grüße,
Martin
Ich habe nun als Workaround einen ActiveX-Button angelegt, der die Liste aus dem Namensbereich „Kundenliste“ neu einliest.
Private Sub KundenRefreshButton_Click()
KundenauswahlBox.ListFillRange = „=Kundenliste“
End Sub
So wie es aussieht, kommt man in einigen Situationen nicht um VBA herum. Danke für den Tipp.
Schöne Grüße,
Martin
Hallo Martin,
die mehrspaltige DropDown-Liste ist interessant. Die Frage, die sich mir dennoch stellt ist: geht anstelle eines Kombinationsfeldes auch ein normales Auswahlfeld über Daten/Gültigkeit bzw Gültigkeitsprüfung?
Viele Grüße Ottmar
Hallo Ottmar,
mit der normalen Gültigkeitsprüfung geht es nur bedingt. Es kann hier als Quelle immer nur der Inhalt einer Spalte angegeben werden, aber es hindert Dich grundsätzlich nichts daran, über eine entsprechende Hilfsspalte in Deiner Quelltabelle 2 oder mehr Spalten über eine VERKETTEN-Funktion zusammenzufügen und diese dann als Dropdown-Werte zu hinterlegen. Allerdings wird dann auch der komplette verkettete Inhalt in die Zelle übernommen.
Grüße,
Martin
Guten Morgen Martin,
als Neuling in der Excel Gemeinde habe ich mich mit zunehmenden Erfolg mit den Tipps und Tricks beschäftigt. Aber nun eine Frage eines noch Unwissenden. Du schreibst du hast mal eben „ein einfaches Auftragsformular erstellt“. Sieht ja auch gut aus, aber WIE erstelle ich so etwas schönes einfaches. Entweder habe ich eine Denkblockade oder einfach zu dumm ich bekomme es einfach nicht hin. Gruß Joe
Hallo Joe,
das ursprüngliche Formular aus dem ersten Screenshot oben besteht nur aus einer einfachen Excel-Tabelle, die über die Funktion „Als Tabelle formatieren“ (in der Registerkarte „Start“) ein wenig ansprechender gestaltet wurde. Damit bekommt man die abwechselnden Streifen mit einem Klick hin.
Ansonsten habe ich einfach noch die Gitternetzlinien ausgeblendet (Register „Ansicht“, Haken bei „Gitternetzlinien“ entfernen).
Mehr steckt da nicht dahinter.
Schöne Grüße,
Martin
Hallo und vielen Dank erstmal für deine tolle Seite! Ich habe mir schon einige Tipps abgeschaut. Leider funktioniert bei mir der ListFillRange Eintrag nicht. Wenn ich den Namen der Tabelle eingebe verschwindet er immer gleich wieder. Woran könnte das liegen?
Hallo Jutta,
ich nehme an, die hast die Quelle als „intelligente“ Tabelle formatiert, richtig (Start – Als Tabelle formatieren)?
Leider lassen sich die Namen von formatierten Tabellen nicht im ListFillRange verwenden. Du musst also die Tabelle in einen normalen Bereich zurückkonvertieren und danach wieder einen Namen für den Bereich festlegen. Dann sollte es funktionieren.
Grüße,
Martin
In Excel 2013 gibt es offensichtlich einen Bug was die ListFillRange betrifft. Abhilfe schaffte hier der Post auf http://blog.contextures.com/archives/2014/09/18/worksheet-combo-box-problem-in-excel-2013/
Hallo Falk,
vielen Dank für den Hinweis und den entsprechenden Link!
Schöne Grüße,
Martin
Warum bietet Ihr die Beispieldatei nicht als Download an?
Hallo dsv,
das hat keinen besonderen Grund. Manchmal gibt es eine Beispieldatei zum Download, und manchmal eben nicht.
Schöne Grüße,
Maritn
Hallo Martin, diese mehrspaltige Auswahlliste ist genau das was ich gesucht habe. Dafür erst einmal ein herzliches Dankeschön. Aber wie im richtigen Leben auch erreicht man nicht immer das Optimum … vielleicht kannst du helfen?
Ich habe eine Excel-Tabelle mit über 4000 Zeilen und jeweils 97 Spalten mit Kontaktmaterial. Diese Liste im Tabellenblatt ‚PROBANT‘ hat den Namen ‚tab_probant‘. Nun möchte ich im 2. Tabellenblatt mittels Dropdown die Daten auslesen. Das funktioniert aber nur, wenn ich unter ListFillRange den Pfad ‚Probant!Spalte:Spalte‘ festlege die ich angezeigt haben möchte; mit dem Ergebnis dass auch die Leerzeilen im Auswahlfeld angezeigt werden.
Gibt es einen Weg diese Leerzeilen nicht auszulesen? Ich danke dir schon jetzt für deine Hilfe und verbleibe
mit freundlichen Grüßen aus dem Hochsauerland
Achim
Hallo Achim,
die Eigenschaft „ListFillRange“ ist leider ziemlich zickig und macht immer wieder Probleme, wenn man versucht, einen definierten Namen anstelle eines Zellbezugs zu verwenden. Manchmal scheint es zu klappen, meistens aber nicht. Daher kann ich hier leider keinen Tipp geben.
Schöne Grüße,
Martin
Hallo Martin,
ich habe meine Excel Tabellen mithilfe der dynamischen dropdown Funktion verknüpft. Habe nun jedoch das Problem, das ich über dieses Dropdown eine weitere Information übertragen möchte. Für einen Tipp wäre ich sehr dankbar.
Gruß Melanie
Auftragsformular
Datum; Material (dynamischer Dropdown); Maße; Stärke (dynamischer dropdown);Menge; Artikelnummer
Materialliste im 2 Tabellenblatt
Material1; Material2; Material3; …
1mm 3mm 1,5mm
2mm 4mm 2mm
3mm 5mm 2,5mm
4mm 6mm 3mm
Wenn ich in der Materialliste jeweils eine zweite Spalte mit der zugehörigen Artikelnummer pro Materialstärke einfüge wird diese Spalte im Dropdown als Spalte1 mit aufgeführt. Das irritiert sehr.
Hallo Melanie,
ich weiß ja jetzt nicht, wie du die dynamischen Dropdown-Listen genau aufgebaut hast. Aber wenn ich es richtig verstanden habe, soll die Artikelnummer automatisch angezeigt werden, wenn ein bestimmtes Material in einer bestimmten Stärke ausgewählt wurde. Das hört sich für mich eher nach einem SVERWEIS mit zwei Suchkriterien an. Vielleicht hilft dir ja dieser Artikel ein wenig weiter:
https://www.tabellenexperte.de/super-sverweis/
Schöne Grüße,
Martin
Hallo Martin, Dein Blog ist toll, -leider habe ich mit den dynamischen, abhängigen Auswahllisten irgendwie keinen Erfolg gehabt, ich bin allerdings auch kein Excel-Guru.
Ich versuche folgendes Szenario in Excel umzusetzen:
Es geht um eine Liste mit Aufgaben, die abgearbeitet werden müssen. Zur Zeit benutzen 3 Abteilungen diese Aufgabenliste, aber nicht jede Aufgabe appliziert für jede Abteilung (nicht applizierende Aufgaben sollen garnicht angezeigt werden). Zusätzlich zu den StandardAufgaben gibt es für auch spezielle Aufgaben, die von Kundenspezifika oder auch Abteilungsspezifika abhängen.
Irgendwie habe ich mich total verzettelt und wäre um Rat oder ein Beispiel dankbar.
Hallo Marko,
das klingt jetzt sehr speziell. Abteilungsspezifische Aufgaben lassen sich vielleicht noch abbilden, aber auch hier müsste man schon ganz genau den Aufbau der Datei und die Abhängigkeiten kennen. Sonst wird es mit einem Tipp schwierig.
Schöne Grüße,
Martin
Hallo Martin, danke für diese Lösung. Habe auch eine wunderbare Auswahlliste bekommen und mir mit SVERWEIS die benötigten Daten geholt. Ein Problem habe ich aber. Die Quelle enthält Namen, Adressen und zwei weitere Angaben. Der Name „Müller“ kommt 3x vor, jeweils mit einer anderen zugehörigen Adresse. Wenn ich den 2. oder 3. „Müller“ auswähle, erscheinen aber immer die Daten des 1. „Müller“ in den SVERWEIS Feldern? Wie kann das gelöst werden? Brauche ich evt. ein zweites Kriterium und wo definiere ich das dann?
OK – habe das Problem selbst gelöst, indem ich in die erste Spalte der Quelle einen eindeutigen Index (Nummer) eingesetzt habe. Das hat genügt…
Hallo Jürgen,
freut mich, dass du eine Lösung gefunden hast. Mein Vorschlag wäre in eine ähnliche Richtung gegangen, ich hätte nur zwei (oder drei) vorhandene Felder miteinander kombiniert, um wieder einen eindeutigen Wert zu erhalten.
Schöne Grüße,
Martin
Frage (oder Hinweis?):
Wenn ich den SVERWEIS in eine WENNNV-Funktion einbette, dann erhalte ich bei fehlenden Kunden zwar den formulierten Text, bei einigen vorhandenen Kunden „zicken“ die SVERWEIS- Werte aber rum? D.h. es werden teilweise die Angaben eines anderen Kunden in die SVERWEIS Zellen eingetragen – und somit falsche Werte weitergegeben. Gibt es dazu Hinweise oder muss ich das so hinnehmen? Habe mal irgendwo gelesen, dass SVERWEIS Probleme machen könnte.
Habe eine andere funktionierende Lösung gefunden. Aber die Problematik interessiert mich…
Hallo Jürgen,
der SVERWEIS macht eigentlich nur dann Probleme, wenn man den dritten Parameter FALSCH vergisst. Nur dadurch ist sichergestellt, dass nach einer exakten Übereinstimmung gesucht wird. Ohne diesen Parameter kann es eben passieren, dass ein anderer, vermeintlich ähnlicher Wert gefunden wird.
Schöne Grüße,
Martin
Danke Martin!
Hallo Martin,
super! Hab das probiert mit einem UserForm da kann ich allerdings die Eigenschaften Linkedcell und Lisfillrange und dann wird meine Liste natürlich nicht angezeigt. Auf das UserForm kann ich verzichten, ich will buchen und das mache ich gleich im Journal mit Deinen Dropdownfelder in der Tabelle
Hallo Wolfgang,
freut mich sehr, dass dir der Tipp weitergeholfen hat. Und insbesondere, wenn damit sogar eine UserForm-Version überflüssig wird.
Schöne Grüße,
Martin