Profi-Tipp: Füllen Sie ein Excel UserForm-Kombinationsfeld ohne VBA aus

Der Artikel vom letzten Monat, "Pro-Tipp: Hinzufügen einer UserForm zur Unterstützung der Dateneingabe in Excel", enthält die Grundlagen zum Erstellen einer Excel-UserForm mit VBA zum Auffüllen von Listensteuerelementen. Um die Liste zu aktualisieren, können Sie das Element zur VBA-Prozedur hinzufügen oder löschen. In diesem Monat werde ich Techniken zum Auffüllen von Kombinations- und Listenfeldsteuerelementen anhand von Daten, die auf Blattebene eingegeben wurden, vorstellen.

Bevor wir anfangen, habe ich zwei Ratschläge für Listen auf Blattebene:

  • Verwenden Sie ein spezielles Blatt für Listen, auch wenn es nur eine Liste gibt. Es besteht die Möglichkeit, dass Sie weitere hinzufügen, sobald Sie den Dreh raus haben, und ein spezielles Blatt finden, das praktisch, effizient und sicher ist. Das dedizierte Listenblatt im Beispiel heißt entsprechend Listen.
  • Geben Sie die Listenelemente nach Möglichkeit in einer Reihenfolge ein, die für die Daten und die Art und Weise, wie Benutzer sie verwenden, sinnvoll ist. Beispielsweise können Sie eine alphabetische oder numerische Reihenfolge verwenden. Es ist nicht immer praktisch, die Listenelemente der Reihe nach einzugeben, aber wenn Sie können, tun Sie dies. Eine Liste nachträglich zu ordnen, erschwert die Sache.

Verwenden Sie einen Bereichsnamen

Der einfachste Weg, eines der Listensteuerelemente von Excel zu füllen, besteht darin, die Liste auf Blattebene einzugeben und der Liste einen Bereichsnamen zu geben. Anschließend können Sie diesen Bereich als Row Source-Eigenschaft des Steuerelements bezeichnen. Es ist die einfachste Methode, aber die am wenigsten flexible. Für den Einstieg benötigen Sie eine Liste wie die in Abbildung A gezeigte. Sie können mit jeder Liste arbeiten oder die Beispieldatei .xls oder .xlsm herunterladen.

Abbildung A.

Geben Sie eine Liste der Elemente in ein spezielles Blatt ein.

Wenden Sie nach Eingabe Ihrer Liste einen Namensbereich an:

  1. Wählen Sie die Liste einschließlich der Überschrift (A1: A6) aus.
  2. Klicken Sie auf die Registerkarte Formeln.
  3. Klicken Sie in der Gruppe Definierte Namen auf Aus Auswahl erstellen.
  4. Excel verwendet im daraufhin angezeigten Dialogfeld standardmäßig die Option "Oberste Zeile" ( Abbildung B ). OK klicken.
    Abbildung B.

Jetzt können Sie die UserForm hinzufügen:

  1. Drücken Sie Alt + F11, um den Visual Basic-Editor (VBE) zu starten.
  2. Wählen Sie im Menü Einfügen die Option UserForm.
  3. Klicken Sie in der Toolbox auf das Kombinationsfeld-Steuerelement und dann auf die Benutzerform, um das neue Steuerelement einzufügen ( Abbildung C ).
    Abbildung C.
  4. Wenn das Eigenschaftenfenster geöffnet ist, werden die Eigenschaften des neuen Steuerelements (das ausgewählte Objekt) angezeigt. Wenn dieses Fenster nicht sichtbar ist, drücken Sie F4 oder wählen Sie im Menü Ansicht die Option Eigenschaftenfenster.
  5. Suchen Sie bei weiterhin ausgewähltem Kombinationsfeld die Eigenschaft Zeilenquelle und geben Sie ClassRange ein ( Abbildung D ). Dies ist der Name des Bereichs (Liste), den Sie zuvor erstellt haben (siehe Abbildung B ).
    Abbildung D.

Das ist es! An diesem Punkt können Sie die neue UserForm testen, indem Sie F5 drücken oder auf die Schaltfläche Makro ausführen in der Standardsymbolleiste von VBE klicken. Abbildung E zeigt das Steuerelement, das geöffnet ist, um seine Liste anzuzeigen. Das war einfach und erforderte kein bisschen VBA.

Abbildung E.

Excel füllt das Steuerelement mit den Daten, die im benannten ClassRange-Bereich gespeichert sind.

Ich habe bereits erwähnt, dass diese Methode zwar wahrscheinlich die einfachste, aber die am wenigsten flexible ist. Hier ist der Grund: Wenn Sie ein Element am Ende dieser Liste hinzufügen, wird dieses neue Element nur dann in der Liste des Steuerelements angezeigt, wenn Sie daran denken, den Bereichsnamen so zu aktualisieren, dass er die neue Zelle enthält. Wenn Sie eine Zeile einfügen und das Element hinzufügen, aktualisiert Excel den Bereichsnamen, um die neue Zeile einzuschließen. Sie müssen sich jedoch daran erinnern, dies zu tun. Wenn die Liste in alphabetischer Reihenfolge vorliegt, müssen Sie außerdem daran denken, sie neu zu sortieren. Es ist keineswegs eine schreckliche Lösung, aber die Reduzierung des Wartungsaufwands ist ein gutes Ziel. Dies ist ein hervorragender Übergang zur nächsten Methode - die Verwendung eines Tabellenobjekts zum Speichern Ihrer Liste.

Bevor wir fortfahren, fragen sich einige von Ihnen, warum ich keine Dynamikbereichsformel vorschlage ("Füllen Sie ein Benutzerformular-Kombinationsfeld in Excel mit einer dynamischen Liste"). In den meisten Situationen ist eine solch komplexe Lösung nicht erforderlich - und sie ist meine am wenigsten bevorzugte, da es schwierig sein kann, einen Dynamikbereich in einer verteilten Arbeitsmappe zu verwalten.

Verwenden Sie ein Tabellenobjekt

Einige Listen sind nicht statisch. Sie möchten Elemente hinzufügen und möglicherweise sogar löschen. Das Tabellenobjekt von Excel zeichnet sich dadurch aus! (Das Table-Objekt ist neu in Excel 2007 und daher in früheren Versionen nicht verfügbar.) Der Vorgang entspricht im Wesentlichen einem benannten Bereich. Anstelle eines Bereichs verwenden Sie jedoch eine Tabelle und verweisen dann auf die Tabelle als Zeilenquelle des Steuerelements Eigentum. Nach wie vor benötigen Sie eine Liste ähnlich der in Abbildung F gezeigten.

Abbildung F.

Geben Sie Ihre Listenelemente ein.

Wenn Ihre ursprüngliche Liste vorhanden ist, können Sie sie in ein Tabellenobjekt konvertieren:

  1. Klicken Sie auf eine beliebige Stelle in der Liste.
  2. Klicken Sie auf die Registerkarte Einfügen.
  3. Klicken Sie in der Gruppe Tabellen auf Tabelle ( Abbildung G ).
    Abbildung G.
  4. Die Standardeinstellungen sind korrekt, klicken Sie also auf OK. Wenn Ihre Liste keine Kopfzeile enthält, deaktivieren Sie diese Option. Excel erstellt das Tabellenobjekt und wendet die Standardtabellenformate an.

Excel weist jeder Tabelle einen Standardnamen zu. Sie müssen diesen Namen kennen. Klicken Sie daher bei ausgewählter Tabelle auf die Registerkarte Design-Kontext. In der Gruppe Eigenschaften (links) zeigt Excel den Standardnamen der Tabelle an. Sie können den Standardnamen wie folgt verwenden oder einen benutzerdefinierten Namen zuweisen:

  1. Klicken Sie in das Steuerelement Tabellenname, um den Namen auszuwählen.
  2. Geben Sie den neuen Namen ein ( Abbildung H ).
    Abbildung H.

Das ist es!

Kehren Sie zur VBE zurück und fügen Sie der UserForm ein zweites Kombinationsfeld-Steuerelement hinzu. Geben Sie bei ausgewähltem Steuerelement ClassTable als Zeilenquelleneigenschaft des neuen Steuerelements ein (siehe Abbildung I) .

Abbildung I.

Legen Sie die Row Source-Eigenschaft des Steuerelements fest.

Drücken Sie F5, um die UserForm auszuführen. Abbildung J zeigt die Liste des zweiten Steuerelements.

Abbildung J.

Das neue Steuerelement zeigt dieselbe Liste an, aber die Quelle ist unterschiedlich.

Bisher war der Prozess fast der gleiche wie bei der zuerst genannten Bereichsmethode, aber hier trennen sich die beiden Methoden. Schließen Sie die UserForm und kehren Sie zum Listenblatt zurück. Fügen Sie dort am Ende der Tabellenliste ein neues Element hinzu. Wählen Sie einfach C6 und geben Sie Reptile ein. Wenn Excel die Tabelle nicht um das neue Element erweitert, löschen Sie es. Wählen Sie dann Zelle C5 aus und drücken Sie die Tabulatortaste, um die Tabelle um C6 zu erweitern, und geben Sie Reptile ein (siehe Abbildung K) .

Abbildung K.

Fügen Sie der Tabelle ein neues Element hinzu.

Kehren Sie zur VBE zurück und drücken Sie F5, um die UserForm auszuführen. Wie Sie in Abbildung L sehen können, enthält die neue Liste Reptile. Sie mussten weder einen Bereichsnamen aktualisieren noch die Liste sortieren. Es funktioniert einfach.

Abbildung L.

Das neue Steuerelement wird automatisch aktualisiert und enthält das neue Element "Reptil".

Keine dieser Methoden erfordert etwas VBA-Code, aber die Table-Methode ist flexibler als die Bereichsnamenmethode. Beachten Sie beim Erstellen Ihrer Listen die Wartung und Benutzerfreundlichkeit.

Senden Sie mir Ihre Frage zu Office

Ich beantworte die Fragen der Leser, wenn ich kann, aber es gibt keine Garantie. Seien Sie so genau wie möglich, wenn Sie mich kontaktieren. Beispiel: "Bitte beheben Sie Fehler in meiner Arbeitsmappe und beheben Sie, was falsch ist" wird wahrscheinlich keine Antwort erhalten, aber "Können Sie mir sagen, warum diese Formel nicht die erwarteten Ergebnisse zurückgibt?" könnte. Ich werde weder von TechRepublic für meine Zeit oder mein Fachwissen erstattet, noch verlange ich von den Lesern eine Gebühr. Sie können mich unter kontaktieren.

© Copyright 2020 | mobilegn.com