In der letzten Woche habe ich hier eine Möglichkeit vorgestellt, mit nur einem einzigen Dropdown-Feld eine ganze Liste in Excel zu befüllen.
Für den Einstieg ganz nett, aber noch nicht so richtig komfortabel.
Heute greife ich das Thema nochmal auf und zeige Dir, wie sich mit wenig Aufwand noch deutlich mehr aus den Steuerfeldern herausholen lässt.
Und so geht’s:
Das Beispiel
Die Idee hinter meinem kleinen Beispiel war es, einen (sehr rudimentären) Dienstplan mit nur einem Dropdown-Feld befüllen wollen. In Spalte D steht das Datum, in der Spalte daneben soll der jeweilige Mitarbeiter eingetragen werden, den man aus einer vordefinierten Auswahlliste selektieren kann.
Beispiel: Der einfache Dienstplan
Die Eingabe soll jedoch nicht direkt in den einzelnen Zeilen in Spalte E erfolgen, sondern über ein einziges Dropdown-Feld.
Im Teil 1 dieser zweiteiligen Artikelserie haben wir dazu das Kombinationsfeld aus den „normale“ Steuerelementen verwendet. Ein Nachteil dieser Lösung war, dass das Steuerelement nur die laufende Nummer des gewählten Eintrags zurückgibt, nicht jedoch den Eintrag selbst. Diesen mussten wir uns über eine einfache INDEX-Funktion selbst dazu holen.
Das geht jedoch noch besser.
Das ActiveX-Kombinationsfeld
Im heutigen zweiten Teil verwenden wir stattdessen das Kombinationsfeld aus den ActiveX-Steuerelementen:
Kombinationsfeld aus den ActiveX-Steuerelementen
Nach dem Einfügen der sogenannten ComboBox an der gewünschten Stelle lässt sich die Position und Größe beliebig ändern, solange sich Excel im Entwurfsmodus befindet:
Der Entwurfsmodus ist aktiviert
Um dieses Steuerelement mit Leben zu füllen, müssen wir noch einen Quellbereich und einen Ausgabebereich definieren.
Der Quellbereich ist die Liste mit den Mitarbeitern, für die ich sinnigerweise den Namen „Mitarbeiter“ vergeben habe:
Für den Ausgabebereich definiere ich wie auch schon im letzten Artikel wieder einen Namen, der sich nicht auf einen fixen Zellbereich bezieht, sondern dynamisch über eine Formel ermittelt wird:
=BEREICH.VERSCHIEBEN(Tabelle1!$E$2;ANZAHL2(Tabelle1!$E:$E)-1;;)
Benannter Bereich als Quelle
Damit wird in Spalte E berechnet, wieviele Einträge bereits vorhanden sind und die aktive Zelle wird damit dynamisch an die nächste frei Position verschoben. Das Prinzip hatte ich schon im letzten Artikel beschrieben, daher gehe ich hier nicht mehr näher darauf ein.
Das Eingabeziel als Formel
Jetzt noch die Zuordnung der beiden benannten Bereiche zum Kombinationsfeld. Dazu muss in den Entwicklertools durch Klick auf die entsprechende Schaltfläche der Entwurfsmodus eingeschaltet werden. Danach lässt sich das eingefügte Kombinationsfeld wieder markieren.
Nun kann man entweder über einen Rechtsklick auf das Objekt oder über die Schaltfläche in den Entwicklertools die Eigenschaften aufrufen:
Eigenschaften der ComboBox aufrufen
Da es sich um ein ActiveX-Steuerelement handelt, sind die Einstellungsmöglichkeiten sehr beachtlich. Uns interessieren für den Moment allerdings nur die beiden Eigenschaften „LinkedCell“ und „ListFillRange“, wo wir die zuvor definierten Namen eintragen:
- LinkedCell = Eingabeziel
- ListFillRange = Mitarbeiter
Das umfangreiche Eigenschaftsfenster
Nun müssen wir nur noch den Entwurfsmodus wieder abschalten und die Schaltfläche macht, was sie soll: In jeder neuen Zeile wird nun direkt der ausgewählte Name eingetragen:
Das Eingabefeld in Aktion
Gezielte Einträge
Schon besser, als im einem normalen Steuerelement aber immer noch sehr statisch. Denn wenn einmal ein Eintrag vorgenommen wurde, lässt er sich über das Dropdown-Feld nicht mehr ändern. Außerdem kann man kein bestimmtes Datum gezielt auswählen.
Das lässt sich aber ganz leicht über ein zweites Eingabefeld ändern. Schieben wir also die ComboBox in Spalte B und verwenden die Zelle A2 dazu, um ein Datum einzugeben (selbstverständlich könnten wir auch hierfür ein Dropdown-Feld einrichten, aber das überspringe ich hier):
Erweiterung um eine Datumsspalte
Nun müssen wir nur noch die Formel für den definierten Namen „Eingabeziel“ etwas anpassen:
=INDEX(Tabelle1!$E$2:$E$11;VERGLEICH(Tabelle1!$A$2;Tabelle1!$D$2:$D$11;0))
Formel im Bereichsnamen anpassen
Was passiert hier? Die allgemeine Syntax der VERGLEICH-Funktion lautet
=INDEX(Matrix; Zeile)
Als Matrix haben wir hier den Bereich mit den Mitarbeitern in Spalte E definiert. Um nun die den gewünschten Eintrag und damit die gesuchte Zeile zu finden, verwenden wir die VERGLEICH-Funktion:
=VERGLEICH(Suchkriterium; Suchmatrix; Vergleichstyp)
Das Suchkriterium ist dabei das Datum, welches wir in A2 eingeben, als Suchmatrix dient die Datumsspalte D. Wichtig ist noch der Vergleichstyp: 0 (Null) bedeutet, dass wir nach einer exakten Übereinstimmung suchen.
Wie man sieht wird der Mitarbeiter immer an dem Tag eingetragen (oder geändert), den wir durch das Datum in A2 vorgegeben haben:
Die verbesserte Version im Einsatz
Es gibt sicherlich deutlich schönere Beispiele, als meinen an den Haaren herbeigezogenen Dienstplan. Aber ich hoffe, du hast das Prinzip verstanden und findest vielleicht ein paar praktische Anwendungsmöglichkeiten.
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.
#Steuerelemente: Das gefällt mir gut!
Hallo, du hast die Steuerelemente wirklich schön veranschaulicht. Ich möchte an dieser Stelle nur einen kleinen Hinweis streuen, warum trotz eingeschränkter Möglichkeiten die Formularsteuerelemente interessant sind: nur diese sind auch beim Apple verfügbar…
Hallo Andreas,
sehr guter Hinweis, das war mir so noch gar nicht bewusst! Vielen Dank dafür.
Schöne Grüße,
Martin
Hallo Martin,
wie immer ein hilfreicher Beitrag. Ich hatte leider bei der Verwendung von ActiveX-Elementen schon das Problem, dass nach MS-Updates plötzlich nichts mehr ging. Daraufhin hatte ich mir angewöhnt nur noch normale Steuerelemente zu verwenden.
Wie sind da deine und die Erfahrungen anderer Nutzer hier?
Danke und weiter so
Kai
Hallo Kai,
danke für das nette Feedback. Update-Probleme hatte ich in diesem Zusammenhang mit einer einzigen Ausnahme (Date and Time Picker) noch, daher kann ich hier wenig dazu sagen. Vielleicht hat da ja ein anderer Leser noch Erfahrungen gemacht.
Schöne Grüße,
Martin
Hallo, Martin,
ganz Klasse, danke. Ich habe eine weiterführende Frage, (siehe unten), hier zunächst zum Hintergrund was ich gemacht habe:
Ich habe eine Ausgabenliste, die folgendermaßen aussieht:
Monat | Betrag | Kategorie1 | Kategorie2 | Kommentar.
Kategorie2 ist eine Unterteilung von Kategorie 1, z.B. habe ich als „Kategorie1“: Lebenshaltungskosten, darunter als „Kategorie2“: Essen&Haushalt; Kleidung; Hobbies; Urlaub etc.
Bisher hatte ich einfach die Tabellenfelder für die Kategorien mit Dropdownlisten hinterlegt. Angeregt durch Deinen Artikel hier habe ich jetzt aber im fixierten Kopf der Liste einen Eingabebereich angelegt, in dem ich über ActiveX-Elemente jeweils die erste leere Zelle in der jeweiligen Spalte per Dropdown befülle. Für die Kategorie2 musste ich mich dafür etwas verrenken, da natürlich – je nach Befüllung von Kategorie1 – eine andere Dropdownliste zum Zug kommt und ich es nicht geschafft habe;die ListFillRange dynamisch (INDIREKT oder so) zu befüllen; aber über eine Zwischen-Dropdownliste geht es.
So weit so super und vielen Dank bis dahin.
Und jetzt zu meiner Anschlussfrage: Ich möchte dasselbe mit der Betrag-Spalte machen, und hier kommt naturgemäß keine Dropdownliste in Frage, sondern ich muss einen absoluten Betrag angeben. Ich habe aber kein ActiveX-Element gefunden, mit dem das funktioniert. Was passiert ist folgendes: Ich nehme z.B. ein Textfeld als ActiveX-Element und schreibe den Betrag „345“ hinein. Daraufhin füllt die Funktion mir NACH JEDEM EINGEGEBENEN ZEICHEN das nächste leere Feld in der Betragsspalte, also stehen untereinander die Beträge „3“, „34“, „345“.
Googlen nach den möglichen Eigenschaften des ActiveX-Elements hat mich nicht weitergebracht. Fällt Dir – oder einem/r anderen Mitlesenden – dazu etwas ein?
Lieben Gruß
Michael
Hallo Michael,
ich fürchte, die dynamische Zellverknüpfung über die BEREICH.VERSCHIEBEN-Funktion beißt sich mit dem Verhalten der ActiveX-Elemente. Sprich: Es funktioniert einfach nicht. Das betrifft übrigens nicht nur das Texteingabefeld. Auch bei der ComboBox tritt dieses Verhalten auf, wenn man den Namen nicht aus der Liste auswählt, sondern einen frei erfundenen per Tastatur eintippt.
Hier habe ich leider auch keine Lösung anzubieten.
Schöne Grüße,
Martin
Oh, und sorry, noch etwas, das auch bei minutiösem Nachbauen Deiner Tabelle in diesem Artikel bei mir nicht richtig geht: Wenn die Lösung, wie von Dir geschildert, fertig ist, funktioniert es nicht, dass man aus dem Dropdown denselben Namen zweimal hintereinander auswählt, z.B. wenn derselbe Mitarbeiter an zwei Tagen hintereinander Dienst hat. Möchtest Du da nochmal draufgucken?
Immer mit dem Vorbehalt, dass ich möglicherweise irgend etwas völlig falsch gemacht habe.
Lieben Gruß
Michael
Hallo Michael,
Du hast nichts falsch gemacht. Zweimal hintereinander denselben Namen auszuwählen geht leider nicht. Excel erkennt nicht, dass sich im Eingabefeld etwas geändert hat und ignoriert die Eingabe daher.
Schöne Grüße,
Martin
Hallo, Martin,
schade – danke jedenfalls für beide Antworten. Und: keep up the good work. Ich habe momentan etwas Zeit und gehe systematisch durch Deine Artikel; dabei habe ich schon unheimlich viel gelernt.
Liebe Grüße
Michael
Guten Tag,
ich habe folgende Situation:
Ich muss eine Bestandserfassung für Fenster durchführen. Dafür habe ich ein Formular entwickelt mit div. Steuerelementen etc. um alles Notwendige erfassen zu können (bis zu ca. 50 Fakten). Das klappt alles, und ich kann ein ausgefülltes Formular je Fenster ausdrucken (was ich auch brauche).
Nun will ich jedoch die erfassten Daten auch weiterverarbeiten können. Ich würde nun für jedes Fenster ein Arbeitsblatt anlegen und suche eine Möglichkeit dann diese Daten gezielt auszulesen. Wenn ich z.B. den Haken setze bei „Glasscheibe gerissen“, möchte ich eine Ausgabe haben in der mir angezeigt wird wie viele Glasscheiben welcher Größe gerissen sind (die Größe wird ebenfalls erfasst).
Bei ein paar Fenstern auf ein paar Tabellenblättern könnte ich das auch händisch machen, aber ich habe ein Gebäude mit ca. 1.300 Fenstern. Ich muss sicher mit mehreren Dateien arbeiten (z.B. je Geschoss eine) um den Überblick nicht zu verlieren, zudem glaube ich mal nicht das Excel mit 1.300 Arbeitsblättern in einer Datei glücklich wird…
Ich würde also z.B. einige Dateien nur zur Datenerfassung anlegen und dann in einer weiteren auf alle Daten zugreifen wollen zur Auswertung.
Hast du dafür einen Lösungsansatz? Ich wäre dir sehr dankbar!
Beste Grüße, Philipp
Hallo Philipp,
ja, 1300 Arbeitsblätter wären definitiv zuviel des Guten 🙂
Ich kann dir hier keine vollständige Lösung aufzeigen. Aber die übliche Herangehensweise wäre in so einem Fall, ein einziges Eingabeformular zu erstellen, das für alle Fenster genutzt werden kann. Dort werden sämtliche Daten erfasst und in eine Datenbank (sprich: separate Tabelle) übertragen. Auf Basis dieser Datenbank kannst Du dann die gewünschten Auswertungen erstellen.
Das Eingabeformular kann entweder mit Excel-Bordmitteln erstellt werden. Vorteil: Ist in kürzester Zeit erledigt. Nachteil: Ganz spartanische Maske ohne Komfort. Wie das geht, habe ich vor längerer Zeit im Excel-Quickie Nr. 36 beschrieben.
Oder Du verwendest Deine Steuerelemente und dazu etwas VBA-Code, um die Eingaben in die Datenbanktabelle zu übertragen.
Vielleicht hilft das ja ein wenig weiter.
Schöne Grüße,
Martin
Ich habe mal eine ganz saublöde Frage:
Ich versuche gerade dieses Beispiel zu verstehen und baue es deshalb nach.
An der Stelle, wo man die Eigenschaften der ComboBox definiert komme ich nicht weiter.
Denn sobald ich den Begriff „Eingabziel“ in das Feld rechts neben dem Eintrag „LinkedCell“ eingegeben haben und irgeneine Taste (Tabulator, ENTER,…) oder die linke Maustaste drücke, um das Feld zu wechseln (zum Feld „ListFillRange“ zu gelangen) wird der Begriff „Eingabeziel“ autpomatisch wieder gelöscht *dumm guck*.
Was mache ich falsch? Wie gebe ich also ein Zielpfad korrekt ein, OHNE dass er gleich wieder gelöscht wird?
Vielen Dank für die Hilfe
Burkhard
Hallo Burkhard,
Voraussetzung ist, dass du zuvor wirklich den Namen „Eingabeziel“ mit der BEREICH.VERSCHIEBEN-Funktion definiert hast. Wenn dieser Name nicht existiert, löscht Excel das Feld sofort.
Schöne Grüße,
Martin
Hallo Martin, wo gebe ich denn den Quell und Ausgabebereich ein?
Hallo Wolfgang,
ist eigentlich oben im Artikel beschrieben (mit Screenshot):
LinkedCell
ListFillRange
Schöne Grüße,
Martin
Hallo
Ich möchte in einer UserForm eine ComboBox mit einer Spalte eines anderen Datenblattes füllen.
Habe ich auch hinbekommen. Funktioniert. Nur ist die Spalte mit einer bedingten Formatierung belegt.
Wie kann ich nun die bedingte Formatierung der Spalte in die ComboBox übernehmen?
Danke schon mal
Gruß
Stefan
Hallo Stefan,
da sieht es schlecht aus. Es ist leider nicht möglich, eine bedingte Formatierungen aus der Datenquelle in die ComboBox zu übernehmen.
Schöne Grüße,
Martin
Hallo Martin,
wenn ich über einen Rechtsklick auf das Objekt in den Entwicklertools die Eigenschaften aufrufen möchte ist dies nicht möglich, da mir diese Option schlichtweg nicht angezeigt wird. Hattest du schonmal dieses Problem, oder weißt, wie man es beheben kann?
Der Microsoft Support weist leider nur darauf hin, dass „Der Befehl Eigenschaften Schaltflächensymbol ist für die Beschriftung Schaltflächensymbol , Schaltflächen Schaltflächensymbol und Gruppenfeld Schaltflächensymbol Formularsteuerelemente nicht aktiviert“ ist, erklärt aber nicht, wie dies zu beheben ist.
[https://support.microsoft.com/de-de/office/warum-sind-formularabh%C3%A4ngige-befehle-oder-steuerelemente-auf-der-multifunktionsleiste-deaktiviert-80be06a7-45d9-4582-9544-dfe87ecb9494]
Vielen Dank
Tim
Hallo Tim,
hast du den Entwurfsmodus aktiviert? Wenn ja, hängt vermutlich davon ab, welches Objekt du tatsächlich angeklickt hast und wo genau. An manchen Stellen sind die Eigenschaften vermutlich einfach nicht verfügbar. Einen konkreteren Tipp kann ich leider auch nicht geben.
Schöne Grüße,
Martin
Hallo,
die Beiträge sind wirklich interessant.
Was aber, wenn ich aus einem Dropdown Kalender (Microsoft Date an Time Picker Control 6.0) ein Datum auswählen möchte und jeweils in eine Zelle (Zellen liegen in einer dynamischen Tabelle untereinander, also in einer Spalte) einfügen möchte.
Zur Erklärung. Es kommt häufig vor, dass ich in Tabellen ein Datum flexibel eingeben muss. Es ist also nicht immer das Datum des jeweiligen Tages (HEUTE-Funktion). Das ganze ist integriert in eine dynamische bzw. intelligente Tabelle, da ich auch nach verschiedenen Spalten sortieren können muss. Jedes Mal das gesamte Datum einzutippen ist aufwändig. Schöner wäre es mit einem Klick auf den Kalender das jeweils angeklickte Datum in die jeweils markierte Zelle setzten zu können.
Ich scheitere ja schon daran, dass es kein ListfillRange beim Kalender gibt.
MfG
P.S. Add-In Funktion ist gesperrt… Wäre natürlich wesentlich einfacher.
Hallo Sabine,
ich fürchte, das wird nur mit VBA-Programmierung umsetzbar sein. Davon abgesehen ist der Date and Time Picker mittlerweile in neueren Excel-Versionen nicht mehr enthalten, daher wäre ich ohnehin vorsichtig mit einer solchen Umsetzung.
Schöne Grüße,
Martin