Autor: Peter Haserodt --- Aus Excel VBA - Gruppe: Steuerelemente
Weitere Artikel der Gruppe: Steuerelemente Aus Excel VBA
Nach oben
Listbox mehrspaltig - Rein - Raus
Autor: Peter Haserodt - Erstellt: -- - Letzte Revision: --
Mehrspaltige Listboxen - Einlesen oder ausgeben (ungebunden)
Multiselect rausschreiben
(Vorbemerkung: Fehlerabfangungen, Prüfen ob der Zielbereich auch realisierbar ist, diesen zu leeren etc... überlasse ich Ihnen)
Für die Beispiele erstellen Sie eine Arbeitsmappe und in Tabelle1 folgendes:
Nun erstellen Sie noch eine Userform mit einer Listbox und drei CommandButtons
Beginnen wir mit dem Einlesen aus einem Range.
Da eine Listbox (MsForms.Listbox) von der Struktur Zeilen - Spalten ist, wie ein Range, kann man recht simpel eine Listbox auf einen Range ansetzen.
Multiselect rausschreiben
(Vorbemerkung: Fehlerabfangungen, Prüfen ob der Zielbereich auch realisierbar ist, diesen zu leeren etc... überlasse ich Ihnen)
Für die Beispiele erstellen Sie eine Arbeitsmappe und in Tabelle1 folgendes:
| Tabelle1 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Beginnen wir mit dem Einlesen aus einem Range.
Da eine Listbox (MsForms.Listbox) von der Struktur Zeilen - Spalten ist, wie ein Range, kann man recht simpel eine Listbox auf einen Range ansetzen.
' **************************************************************
' Modul: UserForm1 Typ = Userform
' **************************************************************
Option Explicit
Private Sub CommandButton1_Click()
ListBoxFuellen ListBox1, ThisWorkbook.Worksheets("Tabelle1").Range("B2:E7")
End Sub
Private Sub ListBoxFuellen(DieListbox As MSForms.ListBox, DerRange As Range)
With DieListbox
.ColumnCount = DerRange.Columns.Count
.List = DerRange.Value
End With
End Sub
Codeanalyse:
Tatsächlich bin ich hier etwas gemein.
Das ganze hätte man einfacher in die Commandbuttonprozedur einbinden können.
Aber dabei lernen Sie auch gleich, wie man Objekte an eine Prozedur übergibt.
(Damit können Sie die Prozedur ListBoxFüllen für mehrere Listboxen nutzen)
Wir übergeben der Prozedur unser Steuerelement - ListBox1 und den Bereich und zwar so, dass es egal ist,
auf welchem Tabellenblatt sich der Anwender gerade befindet und in welcher Arbeitsmappe.
Jetzt kommt aber das eigentlich Spannende:
Eine Listbox hat die Eigenschaft List
Diese ist nichts anderes als eine 2 Dimensionale Liste mit Reihen und Spalten.
Excel VBA stellt uns hier die Möglichkeit zur Verfügung, die List direkt aus einem Range zu füllen.
Aber wir müssen ihr noch mitteilen, wieviel Spalte sie hat - und dies lesen wir aus der Spaltenanzahl des Range heraus.
Danach übergeben wir einfach den Range - so einfach und so simple - oder?
Kommen wir nun zur Ausgabe.
Fügen Sie in dem Code der Userform noch folgendes ein:
Tatsächlich bin ich hier etwas gemein.
Das ganze hätte man einfacher in die Commandbuttonprozedur einbinden können.
Aber dabei lernen Sie auch gleich, wie man Objekte an eine Prozedur übergibt.
(Damit können Sie die Prozedur ListBoxFüllen für mehrere Listboxen nutzen)
Wir übergeben der Prozedur unser Steuerelement - ListBox1 und den Bereich und zwar so, dass es egal ist,
auf welchem Tabellenblatt sich der Anwender gerade befindet und in welcher Arbeitsmappe.
Jetzt kommt aber das eigentlich Spannende:
Eine Listbox hat die Eigenschaft List
Diese ist nichts anderes als eine 2 Dimensionale Liste mit Reihen und Spalten.
Excel VBA stellt uns hier die Möglichkeit zur Verfügung, die List direkt aus einem Range zu füllen.
Aber wir müssen ihr noch mitteilen, wieviel Spalte sie hat - und dies lesen wir aus der Spaltenanzahl des Range heraus.
Danach übergeben wir einfach den Range - so einfach und so simple - oder?
Kommen wir nun zur Ausgabe.
Fügen Sie in dem Code der Userform noch folgendes ein:
Private Sub CommandButton2_Click()
ListBoxRausSimple ListBox1, ThisWorkbook.Worksheets("Tabelle1").Range("K3")
End Sub
Private Sub ListBoxRausSimple(DieListbox As MSForms.ListBox, _
StartZelle As Range)
If DieListbox.ListCount = 0 Then Exit Sub ' Raus wenn nix drin ist
With StartZelle
Range(.Parent.Cells(StartZelle.Row, StartZelle.Column), _
.Parent.Cells(StartZelle.Row + DieListbox.ListCount - 1, _
StartZelle.Column + DieListbox.ColumnCount - 1)).Value = DieListbox.List
End With
End Sub
Codeanalyse - etwas intensiver diesmal
Unsere Prozedur ListboxRausSimple hat natürlich einige Erklärung notwendig.
Zuerstmal die Parent - Geschichte:
Wir übergeben eine Startzelle - in unserem Beispiel die Zelle K3.
Dies soll folgendes machen: Übergebe die Listbox an einen Bereich im Tabellenblatt Tabelle1 beginnend in Zelle K3.
Um es aber unabhängig davon zu machen, dass der Anwender wirklich gerade auf der Tabell1 unseres Workbooks sich befindet, wird die Parenteigenschaft des RangeObjektes benutzt (Die dann auf unser gewünschtes Tabellenblatt verweist.
Hinter Parent steckt somit unsere Arbeitsmappe (ThisWorkbook) und dessen Worksheet Tabelle1
Wichtig aber ist, dass unser Ausgabebereich genau so groß ist, wie unser Listbox.List.
Den Start des Ausgabebereiches ermitteln wir mit der Row und der Column des Ranges.
Bei K3 ist dies die Zeile 3 und die Spalte 11.
Damit muss unser Ausgabebereich bei Cells(3,11) beginnen.
Jetzt kommt die rechte untere Zelle noch dran, die wir ermitteln müssen.
Dies ist genauso einfach:
Zähle die Anzahl der Zeilen in der Listbox (Listbox.ListCount) zum Startbereich Zeilen hinzu und ziehe eins ab.
Im Beispiel (wenn der Gelbe Bereich in der Listbox steht) haben wir 6 Zeilen also:
3 + 6 - 1 ergibt 8 somit ist der Zeilenbereich für K3 : 3 bis 8
Jetzt noch die Spalten:
ColumnCount = 5, K = 11, 11 + 4 - 1 ergibt 14. (und Spalte 14 ist N)
Damit haben wir unsere letzte Zelle.
Unsere Rangeanweisung heisst letztendlich nichts anderes:
--.---.Range(K3:N8) = Liste der Listbox.
Unsere Prozedur ListboxRausSimple hat natürlich einige Erklärung notwendig.
Zuerstmal die Parent - Geschichte:
Wir übergeben eine Startzelle - in unserem Beispiel die Zelle K3.
Dies soll folgendes machen: Übergebe die Listbox an einen Bereich im Tabellenblatt Tabelle1 beginnend in Zelle K3.
Um es aber unabhängig davon zu machen, dass der Anwender wirklich gerade auf der Tabell1 unseres Workbooks sich befindet, wird die Parenteigenschaft des RangeObjektes benutzt (Die dann auf unser gewünschtes Tabellenblatt verweist.
Hinter Parent steckt somit unsere Arbeitsmappe (ThisWorkbook) und dessen Worksheet Tabelle1
Wichtig aber ist, dass unser Ausgabebereich genau so groß ist, wie unser Listbox.List.
Den Start des Ausgabebereiches ermitteln wir mit der Row und der Column des Ranges.
Bei K3 ist dies die Zeile 3 und die Spalte 11.
Damit muss unser Ausgabebereich bei Cells(3,11) beginnen.
Jetzt kommt die rechte untere Zelle noch dran, die wir ermitteln müssen.
Dies ist genauso einfach:
Zähle die Anzahl der Zeilen in der Listbox (Listbox.ListCount) zum Startbereich Zeilen hinzu und ziehe eins ab.
Im Beispiel (wenn der Gelbe Bereich in der Listbox steht) haben wir 6 Zeilen also:
3 + 6 - 1 ergibt 8 somit ist der Zeilenbereich für K3 : 3 bis 8
Jetzt noch die Spalten:
ColumnCount = 5, K = 11, 11 + 4 - 1 ergibt 14. (und Spalte 14 ist N)
Damit haben wir unsere letzte Zelle.
Unsere Rangeanweisung heisst letztendlich nichts anderes:
--.---.Range(K3:N8) = Liste der Listbox.
Kommen wir nun zum Multiselect
Es gibt nun auch - oder gerade - Listboxen, die man auf Multiselect eingestellt hat und nun die selektierten Einträge rausgeben will.
Dies ist nicht ganz so einfach wie bei der gesammten Liste.
Tatsächlich könnte man dies über eine Schleife Zellenweise abarbeiten und diese füllen.
Dies widerspricht aber dem Performance-Gedanken, möglichst Bereiche auf einmal zu füllen.
Ich stelle Ihnen einen Code hier vor, der dies über ein Array löst.
Letztendlich ist es nichts anderes, als das wir eine künstliche List aufbauen.
Sie sollten auch diesen Code genau studieren, denn Sie können dabei viel über Übergaben an Bereiche lernen.
Gemeinerweise überlasse ich es aber Ihnen, diesen Code zu analysieren.
Ändern Sie nun die Eigenschaft Ihrer Listbox auf einen MultiSelectStyle und fügen Sie noch folgenden Code hinzu:
Es gibt nun auch - oder gerade - Listboxen, die man auf Multiselect eingestellt hat und nun die selektierten Einträge rausgeben will.
Dies ist nicht ganz so einfach wie bei der gesammten Liste.
Tatsächlich könnte man dies über eine Schleife Zellenweise abarbeiten und diese füllen.
Dies widerspricht aber dem Performance-Gedanken, möglichst Bereiche auf einmal zu füllen.
Ich stelle Ihnen einen Code hier vor, der dies über ein Array löst.
Letztendlich ist es nichts anderes, als das wir eine künstliche List aufbauen.
Sie sollten auch diesen Code genau studieren, denn Sie können dabei viel über Übergaben an Bereiche lernen.
Gemeinerweise überlasse ich es aber Ihnen, diesen Code zu analysieren.
Ändern Sie nun die Eigenschaft Ihrer Listbox auf einen MultiSelectStyle und fügen Sie noch folgenden Code hinzu:
Private Sub CommandButton3_Click()
ListBoxRausSelected ListBox1, _
ThisWorkbook.Worksheets("Tabelle1").Range("K20")
End Sub
Private Sub ListBoxRausSelected(DieListbox As MSForms.ListBox, _
StartZelle As Range)
Dim vX() As Variant, i As Long, z As Long, k As Integer
' Anzahl der selektierten Zeilen, dürfte schneller as Redim Konstrukte sein
With DieListbox
For i = 0 To .ListCount - 1
If .Selected(i) Then z = z + 1
Next i
If z = 0 Then Exit Sub ' Wenn nichts selektiert
ReDim vX(1 To z, 1 To .ColumnCount) 'Feld dimensionieren
z = 0
For i = 0 To .ListCount - 1 ' Ins Feld schreiben
If .Selected(i) Then
z = z + 1
For k = 0 To .ColumnCount - 1
vX(z, k + 1) = .List(i, k)
Next k
End If
Next i
End With
' Und jetzt raus damit
With StartZelle
Range(.Parent.Cells(StartZelle.Row, StartZelle.Column), _
.Parent.Cells(StartZelle.Row + z - 1, _
StartZelle.Column + DieListbox.ColumnCount - 1)).Value = vX
End With
End Sub
Weitere Artikel der Gruppe: Steuerelemente Aus Excel VBA
Nach oben
rechte seite