Office: Listbox mit mehreren Spalten

Hallo

Ich möchte eine Listbox mit Daten aus einer Tabelle mit mehreren Spalten füllen. Es können neue Daten hinzugefügt oder auch gelöscht werden. Habe lange im Internet gesucht und nur Beispiele gefunden wie man sie mit fixen Bezügen (A1:B30) etc eingibt, aber das suche ich nicht. Dynamische Namen funktionieren auch nicht. Kriege die Meldung "Eigenschaft RowSource konnte nicht gesetzt werden" in meinem Projekt. Es gibt bestimmt eine Möglichkeit die Listbox aus den Zeilen rauszulesen und sie in die listbox zu füllen.

Wie mache ich das am besten/einfachsten? Ich habe mal eine Beispielsdatei hochgeladen. Sage schon jetzt mal "Vielen Dank" für eure Hilfe.

Massimo

Sheet 1

[TABLE]
<tbody>[TR="bgcolor: #cacaca"]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]E[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD]ID[/TD]
[TD]Date[/TD]
[TD]Time[/TD]
[TD]Customer[/TD]
[TD]City[/TD]
[TD] Amount[/TD]
[TD] Orders[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: center"]25[/TD]
[TD="align: right"]13 May 2011[/TD]
[TD="align: center"]15:30:00[/TD]
[TD]Christoph[/TD]
[TD]Prague[/TD]
[TD="align: right"] $ 55'004.00[/TD]
[TD="align: right"] 2'555[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: center"]26[/TD]
[TD="align: right"]18 May 2011[/TD]
[TD="align: center"]13:00:00[/TD]
[TD]Adrian[/TD]
[TD]Moscow[/TD]
[TD="align: right"] $ 55'005.00[/TD]
[TD="align: right"] 800[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: center"]27[/TD]
[TD="align: right"]19 May 2011[/TD]
[TD="align: center"]11:00:00[/TD]
[TD]Tom[/TD]
[TD]Singapore[/TD]
[TD="align: right"] $ 55'006.00[/TD]
[TD="align: right"] 800[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: center"]28[/TD]
[TD="align: right"]23 May 2011[/TD]
[TD="align: center"]16:15:00[/TD]
[TD]Beatrice[/TD]
[TD]Singapore[/TD]
[TD="align: right"] $ 55'007.00[/TD]
[TD="align: right"] 880[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: center"]29[/TD]
[TD="align: right"]23 May 2011[/TD]
[TD="align: center"]10:30:00[/TD]
[TD]Yvette[/TD]
[TD]Singapore[/TD]
[TD="align: right"] $ 55'008.00[/TD]
[TD="align: right"] 850[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: center"]31[/TD]
[TD="align: right"]26 May 2011[/TD]
[TD="align: center"]11:30:00[/TD]
[TD]Bruno[/TD]
[TD]Tokyo[/TD]
[TD="align: right"] $ 55'010.00[/TD]
[TD="align: right"] 220[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: center"]32[/TD]
[TD="align: right"]27 May 2011[/TD]
[TD="align: center"]10:00:00[/TD]
[TD]Steve[/TD]
[TD]Rome[/TD]
[TD="align: right"] $ 55'011.00[/TD]
[TD="align: right"] 333[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]15[/TD]
[TD="align: center"]35[/TD]
[TD="align: right"]03 June 2011[/TD]
[TD="align: center"]14:00:00[/TD]
[TD]Peter[/TD]
[TD]Tokyo[/TD]
[TD="align: right"] $ 55'014.00[/TD]
[TD="align: right"] 750[/TD]
[/TR]
</tbody>[/TABLE]

 

Hi Massimo,

in desem Fall muss man mit ListBox.AddItem arbeiten und jede Zelle einzeln der ListBox zuweisen:

Code:

Private Sub UserForm_Activate()
   Dim lngZeile As Long
   For lngZeile = 2 To IIf(IsEmpty(Cells(Rows.Count, 1)), Cells(Rows.Count, 1).End(xlUp).Row, Rows.Count)
      ListBox1.AddItem Cells(lngZeile, 1)
      ListBox1.List(ListBox1.ListCount - 1, 1) = Cells(lngZeile, 2)
      ListBox1.List(ListBox1.ListCount - 1, 2) = Cells(lngZeile, 3)
      ListBox1.List(ListBox1.ListCount - 1, 3) = Cells(lngZeile, 4)
      ListBox1.List(ListBox1.ListCount - 1, 4) = Cells(lngZeile, 5)
      ListBox1.List(ListBox1.ListCount - 1, 5) = Cells(lngZeile, 6)
      ListBox1.List(ListBox1.ListCount - 1, 6) = Cells(lngZeile, 7)
   Next lngZeile
End Sub

Bis später,
Karin

 

Hallo Massimo und Karin,

das geht doch auch dynamisch:

Code:

Option Explicit

Private Sub Formular_Click()
    Call ListBoxBefüllen
    UserForm1.Show
End Sub

Public Sub ListBoxBefüllen()
    
Dim loLetzteZeile As Long
Dim loLetzteSpalte As Long
Dim strLetzteSpalte As String
    
    With Me
        .Activate   ' wichtig, denn "RowSource" bezieht sich immer auf das aktive Blatt, kann ansonsten zu Fehlern führen!
        loLetzteZeile = IIf(IsEmpty(.Cells(.Rows.Count, 1)), .Cells(.Rows.Count, 1).End(xlUp).Row, .Rows.Count)
        loLetzteSpalte = IIf(IsEmpty(.Cells(1, .Columns.Count)), .Cells(1, .Columns.Count).End(xlToLeft).Column, .Columns.Count)
        strLetzteSpalte = Split(.Cells(1, loLetzteSpalte).Address, "$")(1)  ' Spaltenbuchstabe
    End With
    With UserForm1.ListBox1
        .ColumnCount = loLetzteSpalte                           ' Anzahl der anzuzeigenden Spalten
        .RowSource = "A1:" & strLetzteSpalte & loLetzteZeile    ' anzuzeigender Bereich
    End With
    
End Sub

Voraussetzungen in diesem Fall:
- in der Spalte A ist der letzte Zelleintrag auch wirklich das Listenende
- in der Zeile 1 (Überschriften) ist der letzte Zelleintrag auch in der letzten aufzulistenden Spalte

Sollen die Überschriften in der Listbox nicht angezeigt werden, so ändere:

Code:

.RowSource = "A1:" & strLetzteSpalte & loLetzteZeile    ' anzuzeigender Bereich

in

Code:

.RowSource = "A[COLOR=#ff0000]2[/COLOR]:" & strLetzteSpalte & loLetzteZeile    ' anzuzeigender Bereich

Übrigens, für den Comandbutton würde ich die Eigenschaft "TakeFocusOnClick" auf "False" setzen.
Nur mal so am Rande...



Ich Hoffe geholfen zu haben.

 

Guten morgen Achim und Karin

Ich habe ab Spalte L noch eine weitere Tabelle.
Achim, bedeutet es, dass dein Code nicht funktioniert?
Bin leider nicht Zuhause und habe hier kein Excel.

Gruss
Massimo

 

Hallo Massimo,

doch, mein Code funktioniert trotzdem, er muss dann nur entsprechend angepasst werden.
Wenn die Anzahl der Spalten die Du anzeigen willst immer gleich ist, dann ändere die Zeile:

Code:

loLetzteSpalte = IIf(IsEmpty(.Cells(1, .Columns.Count)), .Cells(1, .Columns.Count).End(xlToLeft).Column, .Columns.Count)

in

Code:

loLetzteSpalte = 7

Dann ist diese nicht mehr dynamisch, sondern statisch.

Wenn die Anzahl der anzuzeigenden Spalten variieren kann, aber rechts daneben trotzdem noch andere Daten stehen, dann müssen wir uns etwas anderes überlegen.
Dafür wären aber dann mehr Informationen nötig.




PS:
Wenn die Spalten wirklich absolut fest sind und sich nie ändern, könnte man den Code sogar wie folgt verkürzen:

Code:

Option Explicit

Private Sub Formular_Click()
    Call ListBoxBefüllen
    UserForm1.Show
End Sub

Public Sub ListBoxBefüllen()
    
Dim loLetzteZeile As Long
    
    With Me
        .Activate   ' wichtig, denn "RowSource" bezieht sich immer auf das aktive Blatt, kann ansonsten zu Fehlern führen!
        loLetzteZeile = IIf(IsEmpty(.Cells(.Rows.Count, 1)), .Cells(.Rows.Count, 1).End(xlUp).Row, .Rows.Count)
    End With
    With UserForm1.ListBox1
        .ColumnCount = 7                        ' Anzahl der anzuzeigenden Spalten
        .RowSource = "A1:G" & loLetzteZeile     ' anzuzeigender Bereich
    End With
    
End Sub

 

Hallo Achim
Die Spalten sind fix. Ich weiss immer wo sich die erste und letzte Spalte befindet.
Es ist mir wichtig, dass ich alle Daten die in den Zeilen sind, in die Listbox geladen werden.
Ich werde deinen letzten Code heute Abend versuchen.
Danke und Gruss
Massimo

Gesendet von meinem GT-I9100 mit Tapatalk 2

 

Hi Massimo,

das ist mit meinem Code überhaupt kein Problem. Außerdem muss das Tabellenblatt dabei nicht aktiv sein, wenn man vor Cells(....) jeweils noch das Worksheet setzt: Worksheets("Tabelle1").Cells(......).

Natürlich kann man das auch anders machen - viele Wege führen nach Rom. Hier noch eine Möglichkeit, ohne direkt auf das Tabellenblatt zugreifen zu müssen, indem die Werte in ein Array eingelesen werden:

Code:

Private Sub UserForm_Activate()
   Dim lngZeile As Long
   Dim arrWerte
   With Worksheets("Tabelle1")
      arrWerte = .Range(.Cells(2, 1), .Cells(IIf(IsEmpty(.Cells(.Rows.Count, 1)), .Cells(.Rows.Count, 1).End(xlUp).Row, .Rows.Count), 7))
   End With
   ListBox1.List = arrWerte
End Sub

Diese Methode hat allerdings den Nachteil, dass die Zellformate nicht übertragen werden.

Und noch eine Möglichkeit mit Zuweisung der RowSource, ohne dass das Tabellenblatt aktiv sein muss:

Code:

Private Sub UserForm_Activate()
   Dim lngLetzte As Long
   With Worksheets("Tabelle1")
      lngLetzte = IIf(IsEmpty(.Cells(.Rows.Count, 1)), .Cells(.Rows.Count, 1).End(xlUp).Row, .Rows.Count)
   End With
   ListBox1.RowSource = "Tabelle5!" & Range("A2:G" & lngLetzte).Address
End Sub

Bis später,
Karin

 

Hallo Karin,
danke, sowas habe ich eh bereits gesucht.
Dann klappt es bei mir so:

Code:

Option Explicit

Private Sub Formular_Click()
    Call ListBoxBefüllen
    UserForm1.Show
End Sub

Public Sub ListBoxBefüllen()
    
Dim loLetzteZeile As Long
    
    loLetzteZeile = IIf(IsEmpty(Me.Cells(Me.Rows.Count, 1)), Me.Cells(Me.Rows.Count, 1).End(xlUp).Row, Me.Rows.Count)
    With UserForm1.ListBox1
        .ColumnCount = 7                        ' Anzahl der anzuzeigenden Spalten
        .RowSource = Me.Name & "!" & Range("A1:G" & loLetzteZeile).Address     ' anzuzeigender Bereich
    End With
    
End Sub

Und wieder was gelernt.

 

Hallo Karin

Vielen Dank für den Tipp. Excel 2010 kann ja über 1 Million Zeilen haben. Wird Excel nicht langsam wenn man End(xlUp).Row benutzt?

Gruss
Massimo

 

Hi Massimo,

wie würdest du denn stattdessen die letzte belegte Zeile ermitteln?

Bis später,
Karin

 

Guten morgen Karin

Ich dachte da eher an etwas mit End(xlDown)?
Verstehe mich nicht falsch: Deine Lösung ist super und habe sie auch implementiert. :-)
Ich frage weil ich dynamische bereiche, die unter Excel 2002 schnell waren, nach den Umstieg auf Excel 2010 langsam waren - bis ich realisierte dass es an den Millionen von Zeilen lag.

Gruss
Massimo

 

Hallo Karin

Ich dachte dass wenn man "von oben" die letzte Zeile ermittelt es schneller ist? ZB mit End(xlDown) - falls sowas geht?
Wir hatten im Betrieb bis vor kurzem XP 2002 und meine dynamische Bereiche waren sehr schnell. Nach dem Umstieg auf XP 2010 war alles quälend langsam, bis ich realisierte dass es daran lag dass alle Zeilen (also all die Millionen) berechnet wurden. Versteh mich nicht falsch: Deine Lösung ist super (und auch Achims) und habe sie implementiert.

Gruss
Massimo

 

Hi Massimo,

keine Angst - an deiner Frage gibt es absolut nichts, was man falsch verstehen könnte. Außerdem ist ein Forum doch dazu da, Fragen zu stellen. :)

Mit End(xlDown) und End(xlUp) ist keine Berechnung verknüpft, was die Schnelligkeit der Ausführung beeinträchtigen würde. Bei Verwendung von End(xlDown) ist jedoch Bedingung, dass zwischendrin keine Leerzellen vorkommen dürfen. Wenn du das garantieren kannst, steht der Verwendung von End(xlDown) natürlich nichts im Weg. Ich habe mir jedoch angewöhnt, bei der Ermittlung der letzten belegten Zelle in jedem Fall mit xlUp zu arbeiten, denn so kann nichts schief gehen. Bei beiden Varianten ist allerdings Voraussetzung, dass die letzte belegte Zell nicht ausgeblendet ist - andernfalls wird nicht die korrekte Zeile ermittelt.

Bis später,
Karin

 

Hallo Karin
Danke für die Information. Werde in dem Fall auch xlup verwenden.

Ich finde es schön, dass man hier Fragen stellen kann , auch sehr einfache ohne sich zu schämen. Ich komme dann in Projekten schneller weiter.

Gruss
Massimo

Gesendet von meinem GT-I9100 mit Tapatalk 2