Ein Listenfeld (Listbox) und ein Dropdown-Feld (ComboBox) ermöglichen dem User die einfache Auswahl von Daten. Während in einem Listenfeld mehrere Einträge sichtbar sind, muss bei einem Dropdown-Feld durch Klicken auf den Pfeil eine Auswahlliste aufgeklappt werden.
Die folgende Abbildung zeigt ein Beispiel für ein Listenfeld mit vier Einträgen.
Im Folgenden wird das Arbeiten mit Listenfeldern beschrieben, gilt aber auch für das Verwenden von Dropdown-Feldern.
Um einem Excel-Formular ein Listenfeld hinzuzufügen, steht das entsprechende Symbol in der Werkzeugsammlung zur Verfügung (Ansicht - Werkzeugsammlung).
Um ein Listenfeld mit Daten zu befüllen gibt es folgende Möglichkeiten:
Im folgenden Beitrag wollen wir die Einträge des Listenfeldes mit Daten aus einer Access Datenbank befüllen. Laden Sie die zip-Datei herunter und extrahieren Sie die Datenbank in ein beliebiges Verzeichnis. Nähere Informationen zum Zugriff auf eine Acces-Datenbank finden Sie im Kapitel Datenbanken.
Die Eigenschaften eines Listenfeldes können entweder im Eigenschaftenfenster in der Entwurfsansicht des Formulars oder direkt über Code geändert werden.
Im Folgenden gehe ich vor allem auf jene Eigenschaften ein, die im Zusammenhang mit den Listeneinträgen von besonderer Bedeutung sind.
Eigenschaft | Bemerkung |
---|---|
ColumnCount | Anzahl der Spalten |
BoundColumn | Rufen Sie auf das Listenfeld die Eigenschaft Value auf, so erhalten Sie den Wert jener Spalte des ausgewählten Eintrags, die mit der Eigenschaft BoundColumn als gebundene Spalte definiert wurde. Die erste Spalte entspricht der Zahl 1. |
ColumnWidths | Ist der Wert nicht gesetzt, so werden die Spalten gleichmäßig verteilt. Geben Sie durch Strichpunkt (Semikolon) getrennt die gewünschten Breiten an, wobei die Angabe auch in Zentimetern erfolgen kann. Fehlt die Angabe für die letzte Zeile, wird diese so breit wie der restlich verfügbare Platz. Beachten Sie, dass beim Ausblenden der ersten Spalte durch Setzen einer Breite von 0 die Eigenschaft Value den Inhalt dieser Spalte liefert, sofern die Eigenschaft BoundColumn auf 1 gesetzt ist. |
ColumnHeads | Anzeige von Spaltenüberschriften, wobei dies nur beim Befüllen des Listenfeldes über die Eigenschaft RowSource funktioniert. |
RowSource | Ein- oder mehrspaltiger Zellbereich, der die Daten für die Einträge des Listenfeldes enthält. Bei einem mehrspaltigen Zellbereich muss die Eigenschaft ColumnCount entsprechend angepasst werden. |
Column | Jeder Eintrag eines Listenfeldes kann über einen 0-basierten Spalten- und Zeilenindex angesprochen werden. Um auf die zweite Spalte des dritten Listeneintrag zuzugreifen, lautet die Anweisung dementsprechend Column(1, 2). |
Nachfolgend die wichtigsten Methoden eines Listenfeldes:
Methode | Bemerkung |
---|---|
AddItem | Fügt einen Eintrag zum Listenfeld hinzu. Das erste Argument gibt den Wert an, der in der ersten Spalte des neuen Eintrags angezeigt wird. Ist das Listenfeld mehrspaltig, so müssen die weiteren Spalten mit der Column-Eigenschaft befüllen. Das zweite Argument gibt den 0-basierten Zeilenindex, an dem der neue Eintrag eingefügt wird. Lassen Sie das zweite Argument weg, um den neuen Eintrag am Ende der Listeneinträge anzufügen. |
RemoveItem | Entfernt einen Eintrag aus dem Listenfeld, wobei zusätzlich die Angabe des 0-basierten Zeilenindex des zu entfernenden Elements anzugeben ist. |
Clear | Entfernt alle Einträge aus dem Listenfeld. |
Wir gehen in den folgenden Codebeispielen von einer Listbox mit dem Namen listPersonen aus, die drei Spalten enthält. In der ersten Spalte soll die Personennummer, in der zweiten Spalte der Nach- und Vorname gemeinsam und in der dritten Spalte das Geburtsdatum angezeigt werden.
Nach dem Erstellen des Datenbankobjekts und dem Öffnen eines Recordsets gehen wir in einer Schleife über alle Datensätze. Bei jedem Schleifendurchlauf werden folgende Aktionen ausgeführt:
Beachten Sie, dass der Spalten- und Zeilenindex beim Aufruf von Column 0-basiert ist. Um auf die zweite Spalte des ersten Eintrages zu verweisen, lautet die Anweisung Column(1, 0).
Folgender Code befindet sich in der Prozedur UserForm_Initialize, die beim Erzeugen des Formulars automatisch aufgerufen wird.
'Listbox füllen mit AddItem
Private Sub UserForm_Initialize()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim zeilenIndex As Integer
Set db = DAO.OpenDatabase("C:\temp\Personenverwaltung.accdb")
Set rs = db.OpenRecordset("Personen")
With Me.listPersonen
.ColumnCount = 3
.ColumnWidths = "2cm"
End With
zeilenIndex = 0
Do Until rs.EOF
With Me.listPersonen
.AddItem rs!PersonNr
.Column(1, zeilenIndex) = rs!Nachname & " " & rs!Vorname
.Column(2, zeilenIndex) = rs!Geburtsdatum
End With
zeilenIndex = zeilenIndex + 1
rs.MoveNext
Loop
rs.Close
db.Close
End Sub
In obigem Beispiel werden die Eigenschaften ColumnCount und ColumnWidths im Code gesetzt. Haben Sie diese Werte bereits im Eigenschaftenfenster des Listenfeldes gesetzt, können Sie die beiden Anweisungen weglassen.
Das Ergebnis sieht wie folgt aus:
Normalerweise wird die erste Spalte, die den Schlüsselwert der Tabelle Personen enthält ausgeblendet. Dies Erreichen Sie, in dem Sie die Breite der ersten Spalte auf 0 cm setzen. Die Eigenschaft Value liefert aber immer noch den Wert dieser ersten ungebundenen Spalte.
'Listbox füllen mit AddItem 'Erste Spalte ausgeblendet Private Sub UserForm_Initialize() Dim db As DAO.Database Dim rs As DAO.Recordset Dim zeilenIndex As Integer Set db = DAO.OpenDatabase("C:\temp\Personenverwaltung.accdb") Set rs = db.OpenRecordset("Personen") With Me.listPersonen .ColumnCount = 3 .ColumnWidths = "0cm" End With zeilenIndex = 0 Do Until rs.EOF With Me.listPersonen .AddItem rs!PersonNr .Column(1, zeilenIndex) = rs!Nachname & " " & rs!Vorname .Column(2, zeilenIndex) = rs!Geburtsdatum End With zeilenIndex = zeilenIndex + 1 rs.MoveNext Loop rs.Close db.Close End Sub
Hier das Listenfeld mit ausgeblendeter Schlüsselspalte:
Die Verwendung der Methode AddItem hat einen schwerwiegenden Nachteil: soll Ihr Listenfeld Spaltenüberschriften anzeigen, gibt es keine Möglichkeit, diese Spaltenüberschriften mit Werten zu befüllen. Dies ist nur bei Verwendung der Eigenschaft RowSource möglich.
Nehmen wir an, in Ihrer Excel-Datei befindet sich ein Tabellenblatt SteuerungPersonen mit folgendem Inhalt:
Setzen Sie im Eigenschaftsfenster die Eigenschaft ColumnHeads auf True und die Eigenschaft RowSource auf SteuerungPersonen!A2:C5. Die Zeile oberhalb dieses Zellbezugs wird automatisch verwendet, um die Spaltenüberschriften zu befüllen.
Um die Daten der Tabelle Personen aus unserer Datenbank im Listenfeld mit Spaltenüberschriften anzuzeigen gehen wir wie folgt vor:
Folgender Code befindet sich in der Prozedur UserForm_Initialize, die beim Erzeugen des Formulars automatisch aufgerufen wird.
'Listbox füllen mit RowSource und ColumnHeads Private Sub UserForm_Initialize() Dim db As DAO.Database Dim rs As DAO.Recordset Dim ws As Excel.Worksheet Dim rangeDaten As Excel.Range Dim zeilenIndex As Integer 'Datenbankzugriff Set db = DAO.OpenDatabase("C:\temp\Personenverwaltung.accdb") Set rs = db.OpenRecordset("Personen") 'Setzen der Eigenschaften der Listbox With Me.listPersonen .ColumnCount = 3 .ColumnWidths = "2cm" .ColumnHeads = True End With Set ws = Worksheets("SteuerungPersonen") 'Entfernen von bestehendem Inhalt ws.UsedRange.ClearContents 'Spaltenüberschriften in Zeile 1 zeilenIndex = 1 With ws .Cells(zeilenIndex, 1) = "Id" .Cells(zeilenIndex, 2) = "Name" .Cells(zeilenIndex, 3) = "Geburtsdatum" End With 'Personendaten beginnend in Spalte 2 zeilenIndex = 2 Do Until rs.EOF With ws .Cells(zeilenIndex, 1) = rs!PersonNr .Cells(zeilenIndex, 2) = rs!Nachname & " " & rs!Vorname .Cells(zeilenIndex, 3).NumberFormat = "dd.mm.yyyy" .Cells(zeilenIndex, 3) = rs!Geburtsdatum End With zeilenIndex = zeilenIndex + 1 rs.MoveNext Loop rs.Close db.Close 'Bereich von B2 bis zum Ende der Daten Set rangeDaten = Range(ws.Cells(2, 1), ws.Cells(zeilenIndex - 1, 3)) 'Setzen von RowSource auf die Adresse des Bereichs (inkl. Tabellenbezeichnung) Me.listPersonen.RowSource = ws.Name & "!" & rangeDaten.Address End Sub
Somit haben wir unser Ziel erreicht: die Daten kommen aus unserer Datenbank und das Listenfeld zeigt Spaltenüberschriften an.
Wie Sie die erste Spalte ausblenden können, ist im vorherigen Kapitel beschrieben.
Um per Code einen Eintrag in dem Listenfeld auszuwählen, setzen Sie die Eigenschaft Selected und geben den gewünschten Zeilenindex an.
Um den ersten Eintrag zu selektieren, schreiben Sie folgende Anweisung:
'Auswahl erster Listeneintrag
Me.listPersonen.Selected(0) = True
Die Eigenschaft ListCount liefert die Anzahl der Listeneinträge. Um den letzten Eintrag zu selektieren, schreiben Sie folgende Anweisung:
'Auswahl erster Listeneintrag
Me.listPersonen.Selected(Me.listPersonen.ListCount - 1) = True
Egal, ob Sie die Auswahl per Code setzen oder der User die Auswahl trifft, das Listenfeld löst das Ereignis AfterUpdate aus.
Um auf das Ereignis reagieren, wählen Sie im oberen Bereich des Formularmoduls im linken Auswahlfeld das Listenfeld (listPersonen) aus. Im rechten Auswahlfeld sehen Sie alle Ereignisse, die das Listenfeld auslösen kann. Wählen sie den Eintrag AfterUpdate. Folgender Code wird automatisch generiert:
'Listenfeld nach Auswahländerung
Private Sub listPersonen_AfterUpdate()
End Sub
Die Eigenschaft ListIndex liefert den 0-basierten Zeilenindex des gewählten Eintrags. Ist kein Eintrag ausgewählt, hat die Eigenschaft ListIndex den Wert -1.
Die Eigenschaft Value liefert den Wert der gebundenen Spalte (BoundColumn).
Geben Sie bei der Eigenschaft Column nur den 0-basierten Spaltenindex an, erhalten Sie den Wert der entsprechenden Spalte des gewählten Eintrags.
Im folgenden Beispiel wird geprüft, ob der User eine Auswahl getroffen hat. Ist der ListIndex ungleich -1, so werden die Werte der entsprechenden Eigenschaften im Ausgabefenster ausgegeben.
'Listenfeld nach Auswahländerung
Private Sub listPersonen_AfterUpdate()
With Me.listPersonen
If .ListIndex <> -1 Then
Debug.Print "ListIndex: " & .ListIndex
Debug.Print "Value: " & .Value
Debug.Print "Zweite Spalte: " & .Column(1)
Debug.Print "Dritte Spalte: " & .Column(2)
End If
End With
End Sub
Somit können Sie auch abhängige Listenfelder erstellen. Wird im ersten Listenfeld eine Auswahl getroffen, so wird das zweite Listenfeld mit entsprechenden Werten befüllt. Beispielsweise könnte das erste Listenfeld die Abteilungen Ihrer Organisation anzeigen. Bei Auswahl einer Abteilung werden in einem zweiten Listenfeld alle dazugehörigen MitarbeiterInnen angezeigt.
Die gezeigten Codebeispiele können Sie als xlsm-Datei herunterladen.