alle gefüllten Spalten einer Zeile in Listbox

Moderator: ModerationP

alle gefüllten Spalten einer Zeile in Listbox

Beitragvon Kubikossi » 14. Sep 2021, 06:31

Hallo zusammen,

Ich hatte vor 5 Jahren mal eine kleine Lagerverwaltung über Excel VBA gemacht mit Hilfe der Experten hier.
In der Userform gab es auch eine Textsuchfunktion (umschaltbar Volltext oder Teilsuche), bei der alle gefundenen Einträge in einer Listbox angezeigt wurden, mitsamt den einzelnen Spalten.

Jetzt hab ich eine neue Userform, in welche ich die Suche übernommen habe. Ich habe lediglich die Namen von Sheet und Textfeld angepasst.
Das funktioniert soweit auch, allerdings wird mir in der Listbox immer nur die erste Spalte einer jeden Zeile angezeigt, während in der alten Userform alle Spalten angezeigt wurden.
Hab in der Listbox im Eigenschaftenfenster auch die Spaltenbreiten eingetragen (ColumnWidths 30 Pt;30 Pt;30 Pt;30 Pt;40 Pt;40 Pt;75 Pt...), hilft aber nix.

Hat jemand ne Idee, woran das liegen kann?
Hier ist der Code dazu:
Code: Alles auswählen
Private Sub bTextsuche_Click()

Dim rngFind As Range
  Dim oWsh As Object
  Dim FirstAddress As String
  Dim zeile As Long, i, Look
  Set oWsh = ThisWorkbook.Sheets("Übersicht")
  With oWsh.Range("A:Q")
    If Me.tProduktbezeichnung = "" Then Exit Sub
    Look = IIf(Me.OVoll, xlWhole, xlPart)
    Set rngFind = .Find(What:=Me.tProduktbezeichnung, LookAt:=Look)
    Me.ListBox1.Clear
    If rngFind Is Nothing Then
      MsgBox Me.tProduktbezeichnung & " nicht vorhanden", vbInformation, "nichtsgefunden"
    Else
      FirstAddress = rngFind.Address
      Do
        zeile = rngFind.Row
        Me.ListBox1.AddItem oWsh.Cells(zeile, 1)
        For i = 2 To 4
          Me.ListBox1.List(Me.ListBox1.ListCount - 1, i - 1) = oWsh.Cells(zeile, i)
        Next i
        Set rngFind = .FindNext(rngFind)
      Loop Until rngFind.Address = FirstAddress
    End If
  End With
 
End Sub


Danke schon mal für die Mühe

Gruß
Rene
Kubikossi
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 15
Registriert: 22. Jun 2021, 12:38

Re: alle gefüllten Spalten einer Zeile in Listbox

Beitragvon @Mase » 14. Sep 2021, 07:01

Moin moin,

lass das .AddItem weg... kost Dich nur unnötig Zeit.
Pack alles in ein Array und weise das Array der .List-Eigenschaft zu.

Zuvor stell sicher, dass die .ColumnCount-Eigenschaft der Listbox der Anzahl Elemente der zweite Array-Dimension übereinstimmt.

Kurz gesagt:
Listbox mehrere Spalten => Listbox.ColumnCount anpassen
.List-Eigenschaft => Array(2Dimensional)
@Mase
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 17
Registriert: 08. Sep 2021, 06:51

Re: alle gefüllten Spalten einer Zeile in Listbox

Beitragvon Kubikossi » 14. Sep 2021, 07:48

Listbox.ColumnCount ... ähm ja danke... das hatte ich vergessen anzupassen.
Was ist ein Array? Bin leider Anfänger.

Was jetzt noch Probleme macht ist der Teil im Code:
Code: Alles auswählen
For i = 2 To 10
          Me.ListBox1.List(Me.ListBox1.ListCount - 1, i - 1) = oWsh.Cells(zeile, i)


10 ist bei "For i = 2 To 10" das Maximum, was ich eintragen kann. Dann zeigt es mir die ersten 10 Spalten an. (Keine Ahnung warum das damals im Code so gelöst wurde)
Sobald ich höher gehe (und ich brauche mind. die ersten 20 Spalten angezeigt), meckert er die nächste Zeile an. Eigenschaft List konnte nicht gesetzt werden. Ungültiger Eigenschaftswert.
Kubikossi
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 15
Registriert: 22. Jun 2021, 12:38

Re: alle gefüllten Spalten einer Zeile in Listbox

Beitragvon Flotter Feger » 14. Sep 2021, 09:29

Hallo,

Code ist ungetestet, da keine Musterdatei vorhanden.

So ungefähr ... deine Code ab der betreffenden Zeile austauschen.
Code: Alles auswählen
    If rngFind Is Nothing Then
      MsgBox Me.tProduktbezeichnung & " nicht vorhanden", vbInformation, "nichtsgefunden"
    Else
      FirstAddress = rngFind.Address
      Do
        zeile = rngFind.Row
        r = r + 1
        ReDim Preserve arr(1 To 20, 1 To r)
        For i = 1 To 20
            arr(i, r) = oWsh.Cells(zeile, i)
        Next i
        Set rngFind = .FindNext(rngFind)
      Loop Until rngFind.Address = FirstAddress

      ListBox1.Column = arr
    End If
  End With

Sabina
Benutzeravatar
Flotter Feger
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 3255
Registriert: 24. Okt 2016, 16:40

Re: alle gefüllten Spalten einer Zeile in Listbox

Beitragvon Kubikossi » 14. Sep 2021, 10:13

Flotter Feger hat geschrieben:Hallo,

Code ist ungetestet, da keine Musterdatei vorhanden.

So ungefähr ... deine Code ab der betreffenden Zeile austauschen.
Code: Alles auswählen
    If rngFind Is Nothing Then
      MsgBox Me.tProduktbezeichnung & " nicht vorhanden", vbInformation, "nichtsgefunden"
    Else
      FirstAddress = rngFind.Address
      Do
        zeile = rngFind.Row
        r = r + 1
        ReDim Preserve arr(1 To 20, 1 To r)
        For i = 1 To 20
            arr(i, r) = oWsh.Cells(zeile, i)
        Next i
        Set rngFind = .FindNext(rngFind)
      Loop Until rngFind.Address = FirstAddress

      ListBox1.Column = arr
    End If
  End With

Sabina

Danke Sabrina....
als welche Variable muss "r" definiert werden? Das fehlt noch.
Und für was ist "r" genau zuständig? Sorry für die wahrscheinlich blöde Frage, versuch das nur zu verstehen.
Kubikossi
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 15
Registriert: 22. Jun 2021, 12:38

Re: alle gefüllten Spalten einer Zeile in Listbox

Beitragvon Flotter Feger » 14. Sep 2021, 12:44

Hallo,

'r' ist ein Zähler, der in meinem Namen nicht vorkommt. Dim r As Long ist dafür OK.
Die Variable ist dafür da, die Anzahl der Zeilen im Array arr (Dim arr As Variant) zu definieren und das Array mittels ReDim Preserve, um einen neuen Eintrag zu erweitern, wenn ein Fund gemacht wird.
Das Array wir hier 'hochkant' erstellt und nicht quer, wie die Zeilen in der Tabelle stehen, deshalb auch die Übergabe des Arrays an ListBox1.Column ... und nicht an ListBox1.List.

Sabina ... ohne 'r'
Benutzeravatar
Flotter Feger
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 3255
Registriert: 24. Okt 2016, 16:40

Re: alle gefüllten Spalten einer Zeile in Listbox

Beitragvon Kubikossi » 14. Sep 2021, 13:01

Flotter Feger hat geschrieben:Hallo,

'r' ist ein Zähler, der in meinem Namen nicht vorkommt. Dim r As Long ist dafür OK.
Die Variable ist dafür da, die Anzahl der Zeilen im Array arr (Dim arr As Variant) zu definieren und das Array mittels ReDim Preserve, um einen neuen Eintrag zu erweitern, wenn ein Fund gemacht wird.
Das Array wir hier 'hochkant' erstellt und nicht quer, wie die Zeilen in der Tabelle stehen, deshalb auch die Übergabe des Arrays an ListBox1.Column ... und nicht an ListBox1.List.

Sabina ... ohne 'r'

Hi nochmal Sabina ohne 'r' ;)

dann hatte ich das mit Dim r As Long schon richtig probiert.
Da kam dann aber "ReDim Preserve arr(1 To 20, 1 To" als Variable nicht definiert als nächster Fehler.
Daher dachte ich, das sei dann doch falsch gewesen.
Also passt dann in dieser Zeile doch etwas nicht.

Gruß
Rene mit r
Kubikossi
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 15
Registriert: 22. Jun 2021, 12:38

Re: alle gefüllten Spalten einer Zeile in Listbox

Beitragvon Flotter Feger » 14. Sep 2021, 13:38

Hallo,

ich sagte doch ungetestet ...

Dim arr() As Variant

Sabina
Benutzeravatar
Flotter Feger
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 3255
Registriert: 24. Okt 2016, 16:40

Re: alle gefüllten Spalten einer Zeile in Listbox

Beitragvon slowboarder » 14. Sep 2021, 14:44

Hi
mal ne ganz andere Herangehensweise:

a) in der Tabelle:
1. lege dir eine freie Zelle ausserhalb der Lagerliste fest, in die du den Suchbegriff schreibst (z.B Z1)
2. schreibe in die erste freie Spalte (U?) der Lagertabelle folgende Formel, mit der du kennzeichnest ob der Suchbegriff vorkommt oder nicht: =Wenn(ZählenWenn(A2:Q2;$Z$1);1;"")

b) im Suchmakro:
1. schreibe den Suchtext in die Zelle Z1. Wenn mit der Suchoption "enthält" gesucht wird, fügst du noch die "*" hinzu:
Code: Alles auswählen
oWsh.Range("Z1").Value = IIF(Me.OVoll, Me.tProduktbezeichnung, "*" & Me.tProduktbezeichnung & "*")

2. sortiere jetzt die Liste nach der Spalte U, so dass alle Zeilen, die in die Listbox sollen, direkt untereinander und direkt unter der Überschrift stehen.
Code: Alles auswählen
oWsh.Usedrange.Sort Key1:=oWsh.Cells(1, "U"), order1:=xlascending, header:=xlyes

3. wenn du so sortiert hast, kannst du die Werte direkt in die Listbox schreiben:
Code: Alles auswählen
Listbox1.List = oWsh.Cells(2, 1).Resize(Worksheetfunction.Sum(oWsh.Range("U:U"), 20).Value


somit kommst du mit ganz wenigen Programmschritten zu deinem Ziel.
(man muss halt mit der Umsortierung der Liste einverstanden sein)

diese Methode hätte noch einen weitern Vorteil:
oft wünscht man sich bei einer Listbox mit vielen Spalten Überschriften für diese Spalten.
man kann die Spaltenüberschriften auch aus der Tabellen übernehmen, so dass sie sich automatisch an die Spaltenbreiten und ans horizontale Scrolling anpassen, in dem man in der Listbox die Eigenschaft ColumnHeads = True setzt.
Dies Funktioniert aber nur, wenn man die Listbox direkt aus der Tabelle mit RowSource übernimmt.
Und genau das ist bei dieser Methode möglich, da durch die Sortierung die Zeilen, die in die Listbox sollen, direkt unter der Überschrift stehen, damit könnte man RowSource nutzen und als Schritt 3 programmieren:
Code: Alles auswählen
Listbox1.RowSource = "'" & oWsh.name & "'!A2:T" & worksheetfunction.Sum(oWsh.Range("U:U")) + 1


gruß Daniel
slowboarder
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 28445
Registriert: 18. Apr 2009, 13:33

Re: alle gefüllten Spalten einer Zeile in Listbox

Beitragvon snb » 14. Sep 2021, 17:39

Oder
Du hast keine ausreichende Berechtigung, um die Dateianhänge dieses Beitrags anzusehen.
snb
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 8711
Registriert: 25. Sep 2014, 16:37

Re: alle gefüllten Spalten einer Zeile in Listbox

Beitragvon Kubikossi » 15. Sep 2021, 04:51

slowboarder hat geschrieben:
Flotter Feger hat geschrieben:Hallo,

ich sagte doch ungetestet ...

Dim arr() As Variant

Sabina

Danke, jetzt funktioniert es.


slowboarder hat geschrieben:Hi
mal ne ganz andere Herangehensweise:

a) in der Tabelle:
1. lege dir eine freie Zelle ausserhalb der Lagerliste fest, in die du den Suchbegriff schreibst (z.B Z1)
2. schreibe in die erste freie Spalte (U?) der Lagertabelle folgende Formel, mit der du kennzeichnest ob der Suchbegriff vorkommt oder nicht: =Wenn(ZählenWenn(A2:Q2;$Z$1);1;"")

b) im Suchmakro:
1. schreibe den Suchtext in die Zelle Z1. Wenn mit der Suchoption "enthält" gesucht wird, fügst du noch die "*" hinzu:
Code: Alles auswählen
oWsh.Range("Z1").Value = IIF(Me.OVoll, Me.tProduktbezeichnung, "*" & Me.tProduktbezeichnung & "*")

2. sortiere jetzt die Liste nach der Spalte U, so dass alle Zeilen, die in die Listbox sollen, direkt untereinander und direkt unter der Überschrift stehen.
Code: Alles auswählen
oWsh.Usedrange.Sort Key1:=oWsh.Cells(1, "U"), order1:=xlascending, header:=xlyes

3. wenn du so sortiert hast, kannst du die Werte direkt in die Listbox schreiben:
Code: Alles auswählen
Listbox1.List = oWsh.Cells(2, 1).Resize(Worksheetfunction.Sum(oWsh.Range("U:U"), 20).Value


somit kommst du mit ganz wenigen Programmschritten zu deinem Ziel.
(man muss halt mit der Umsortierung der Liste einverstanden sein)

diese Methode hätte noch einen weitern Vorteil:
oft wünscht man sich bei einer Listbox mit vielen Spalten Überschriften für diese Spalten.
man kann die Spaltenüberschriften auch aus der Tabellen übernehmen, so dass sie sich automatisch an die Spaltenbreiten und ans horizontale Scrolling anpassen, in dem man in der Listbox die Eigenschaft ColumnHeads = True setzt.
Dies Funktioniert aber nur, wenn man die Listbox direkt aus der Tabelle mit RowSource übernimmt.
Und genau das ist bei dieser Methode möglich, da durch die Sortierung die Zeilen, die in die Listbox sollen, direkt unter der Überschrift stehen, damit könnte man RowSource nutzen und als Schritt 3 programmieren:
Code: Alles auswählen
Listbox1.RowSource = "'" & oWsh.name & "'!A2:T" & worksheetfunction.Sum(oWsh.Range("U:U")) + 1


gruß Daniel

Hallo Daniel,

danke erst mal für deine Alternative. Auch das werd ich mal testen, wenn ich's denn so hinbekomme wie du es beschreibst.
Das mit den Spaltenüberschriften ist nicht schlecht. ColumnHeads hatte ich hier auch schon auf True gesetzt, bekomme da aber nur leere Felder angezeigt.

Gruß
Rene
Kubikossi
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 15
Registriert: 22. Jun 2021, 12:38

Re: alle gefüllten Spalten einer Zeile in Listbox

Beitragvon slowboarder » 15. Sep 2021, 08:44

Die ColumnHeads gehen zur, wenn du die Listbox über RowSource mit einer Exceltabelle verknüpfst.
Als Überschrift wird die Zeile über der ersten RowSource-Zeile verwendet.
Gruß Daniel
slowboarder
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 28445
Registriert: 18. Apr 2009, 13:33


Zurück zu Excel Forum (provisorisch)

Wer ist online?

Mitglieder in diesem Forum: GMG-CC und 20 Gäste