Excel-Tricks mit Steuerelementen (Teil 2) 10

Im Teil 2 der Artikelserie geht es um das Kombinationsfeld aus den ActiveX-Steuerelementen.
 

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

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

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

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

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

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

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

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

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

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

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

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.

 

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

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.



Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

10 Gedanken zu “Excel-Tricks mit Steuerelementen (Teil 2)

  • Andreas Katits

    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…

    • Martin Weiß Autor des Beitrags

      Hallo Andreas,

      sehr guter Hinweis, das war mir so noch gar nicht bewusst! Vielen Dank dafür.

      Schöne Grüße,
      Martin

  • Kai Kießling

    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

    • Martin Weiß Autor des Beitrags

      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

  • Michael Krüger

    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

    • Martin Weiß Autor des Beitrags

      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

  • Michael Krüger

    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

    • Martin Weiß Autor des Beitrags

      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

      • Michael Krüger

        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