Excel-Tricks mit Steuerelementen (Teil 2) 5

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.



Kommentar erstellen

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

5 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ß Beitragsautor

      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ß Beitragsautor

      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