Office: (Office 365) Listbox befüllen

Hallo mein Name ist Carsten und ich bin neu hier im Forum und habe gleich eine Frage. Ich habe eine Userform mit verschiedenen voneinander abhängigen Listboxen. Das klappt auch soweit. Nur der Click von LB2 auf LB3 bereitet mir Problem. Und zwar folgendes. Wenn ich die Daten einfach so nebeneinander in die Spalten der Tabelle aus der die LB befüllt wird schreibe funktioniert es. Die Einträge in den Spalten sind unterschiedlich lang und es kommen jeden Tag neue Spalten hinzu. Sobald ich die Tabelle zum befüllen aber als intelligente Tabelle formatiere werden mir in LB3 auch die leeren Zeilen angezeigt da wie beschrieben SpalteA z.B. 28 Einträge hat, SpalteB 12, SpalteC 57 usw. Hier der Code mit dem ich arbeite. Als wenn der Code nicht den letzten Eintrag der dazugehörigen Spalte ermitteln würde sondern imm er von der längsten ausgeht. Was mache ich falsch?
Private Sub ListBox2_Click
listefuellen _
wks:=Worksheets("Info"), zaehleranfang:=2, listboxName:="ListBox3", rowdirection:=True _
, spalte:=Worksheets("Info").Range("1:1").Find(ListBox2, , xlValues, xlWhole, xlByColumns, xlPrevious, False, False).Column
und die Funktion:
Private Function listefuellen(wks As Worksheet, zaehleranfang As Long, spalte As Long, listboxName As String, rowdirection As Boolean)
Dim i As Long
Dim objctrl As Object
Dim zaehlerende As Long
If rowdirection Then
zaehlerende = wks.Cells(Cells.Rows.Count, spalte).End(xlUp).Row
Else
zaehlerende = wks.Cells(1, Cells.Columns.Count).End(xlToLeft).Column
End If
Set objctrl = Me.Controls(listboxName)
objctrl.Clear
For i = zaehleranfang To zaehlerende
objctrl.AddItem IIf(rowdirection, wks.Cells(i, spalte), wks.Cells(spalte, i))
Next

Set objctrl = Nothing
End Function
Vielen Dank im voraus und ich hoffe ich konnte es erklären
Carsten

 

Hallo Carsten,

habe deine Beschreibung nicht bis zum Ende gelesen. Stelle bitte eine Beispieldatei ein, dann kann man sehen, was man machen kann.

 

Hallo Carsten,

spontan würde ich tippen, dass dein Fehler hier liegt:

Code:

wks:=Worksheets("Info"), zaehleranfang:=2, listboxName:="ListBox3", rowdirection:=True _
, spalte:=Worksheets("Info").Range("1:1").Find(ListBox2, , xlValues, xlWhole, xlByColumns, xlPrevious, False, False).Column

Müsste da nicht ....Find(ListBox3, ,... stehen? Sonst suchst Du ja die letzte Zeile der Spalte für Listbox2, oder?

Gruß,
Lutz

 

Hallo Lutz, das ist nicht das Problem. Es funtioniert ja nur nicht wenn ich die Tabelle zum befüllen als "intelligente Tabelle" formatiere. Sobald ich sie in einen Bereich konvertiere funktioniert alles super. Aber wie von Klaus Dieter gewünscht stelle ich mal die Datei ein. In der Tabelle mit Namen Modul könnt ihr das sehen. Zur Zeit ist sie als intelligente Tabelle formatiert. Konvertiert sie in einen Bereich und es funktioniert.
Danke an alle Carsten

 

Hallo Nanu,

bevor ich mich damit intensiver beschäftige zwei Anmerkungen, bzw. eine Frage und eine Anmerkung. Warum verwendest du Listenfelder, das würde ja auch mit Kombinationsfeldern funktionieren. Du kannst schon mit intelligenten Tabellen arbeiten, allerdings solltest du die für jedes Objekt (Listenfeld bzw. Kombinationsfeld) als eigenständige Liste anlegen, dann werden auch neu zugefügte Begriffe berücksichtigt.

 

Hallo Klaus Dieter. Die Sache ist folgende. Ich bin ein Laie in VBA und das habe ich mir durch vieles suchen im Internet sagen wir mal zusammengebastelt. Es funktioniert ja auch bis auf die Kleinigkeit das er mir in Listbox3 je nach Suchergebnis in Listbox2 irgendwie nicht bis zur letzten beschriebenen Zelle sucht sondern mir alle Leerzeilen mit übernimmt. Ich kann da auch mit leben, ich dachte halt nur, wenn jemand mit Ahnung den Code sieht der kann mir vielleicht sagen wo ich den Fehler habe. Zur Not arbeite ich auch mit einer "normalen Tabelle" und nicht mit einer "Intelligenten". Falls du dir trotzdem die Mühe machst bin ich natürlich dankbar. So lerne ich wieder etwas dazu.
Vielen Dank Carsten

 

Hallo Carsten,
der Uwe (Alwin) aus dem anderen Forum meldet sich hier zurück.
Die Ursache ist ganz simpel. Die 3. Listbox wird nicht gefüllt, weil der Listindex der Listbox = -1 ist. Damit wird das Füllen der Listbox3 via der Function verhindert. Es hat also nichts mit dem (Strg./T Tabelle) .Listobjects zu tun.
Entferne:

Code:

If ListBox2.ListIndex <> -1 Then

und dann klappt das auch.
Wenn du vor hast die Tabellen als Strg./T Tabellen zu verwenden, macht es eigentlich auch Sinn den Code auf .Listobjects aufzubauen.

Gruß Uwe

 

Hallo Uwe, herzlichen Dank das du dich weiter mit meinen Problemchen befasst. Ich glaube ich erkläre es schlecht. Die Listbox3 wird ja befüllt, das klappt ohne Probleme. Auch mit dem vorhandenen Code. Das Problem ist das der Code mir irgendwie nicht den letzten Eintrag der ermittelten Spalte findet sobald ich die Tabelle als Strg./T formatiere. Auch wenn nur 2 Einträge in der LB3 erscheinen ist rechts in der LB3 die Bildlaufleiste zu sehen und ich kann in die leeren Einträge klicken. Konvertiere ich sie wieder in einen Bereich klappt es wie gewollt. Wie beschrieben wenn sich jemand die Zeit nehmen möchte probiert es mal aus. Einfach das Tabellenblatt "Modul" aufrufen und die ich sage mal Tabellenart wechseln. Strg./T Bildlaufleiste da, auch bei wenigen Einträgen, in Bereich konvertieren und Bildlaufleiste weg. Kommt da eben nur wenn es viele Einträge sind.
Vielen Dank Carsten

 

Hallo Carsten,
Das Problem ist der Aufbau Function in Verbindung mit der Funktionalität von Strg./T Tabellen.
Der Lösungsweg ohne die Function über den Haufen zu schmeißen wäre so:
Aus allen Tabellen eine Strg./T machen und die Function dann so:

Code:

Private Function listefuellen(wks As Worksheet, zaehleranfang As Long, spalte As Long, listboxName As String, rowdirection As Boolean)
    Dim i As Long
    Dim objctrl As Object
    Dim zaehlerende As Long
 
    If rowdirection Then
        zaehlerende = wks.ListObjects(1).DataBodyRange.Rows.Count + 1
    Else
        zaehlerende = wks.ListObjects(1).DataBodyRange.Columns.Count
    End If
 
    Set objctrl = Me.Controls(listboxName)
    objctrl.Clear
 
    For i = zaehleranfang To zaehlerende
        If wks.Cells(i, spalte) <> "" Then
            objctrl.AddItem IIf(rowdirection, wks.Cells(i, spalte), wks.Cells(spalte, i))
        End If
    Next

    Set objctrl = Nothing
End Function

Das ist so aber ziemlicher Mist und nur als Notlösung zu betrachten.

Gruß Uwe

 

Hallo,

mir sind folgende 2 Dinge aufgefallen:
1) Die Daten im Arbeitsblatt "Nummer" müssten wie folgt geändert werden (siehe Zeile 1 und 2 !!):


2) Die Funktion "listefuellen" müsste man wie folgt ergänzen (siehe 3 Zeilen in "If rowDirection..."):

Code:

Private Function listefuellen(wks As Worksheet, zaehlerAnfang As Long, Spalte As Long, listboxName As String, rowDirection As Boolean)
 Dim i As Long
 Dim objCtrl As Object
 Dim zaehlerEnde As Long

 If rowDirection Then
    zaehlerEnde = wks.Cells(Cells.Rows.Count, Spalte).End(xlUp).Row
    If wks.Cells(zaehlerEnde, Spalte) = "" Then                       '<-- ergänzen
       zaehlerEnde = wks.Cells(zaehlerEnde, Spalte).End(xlUp).Row     '<-- ergänzen
    End If                                                            '<-- ergänzen
 Else
    zaehlerEnde = wks.Cells(1, Cells.Columns.Count).End(xlToLeft).Column
 End If

 Set objCtrl = Me.Controls(listboxName)
 objCtrl.Clear

    For i = zaehlerAnfang To zaehlerEnde
      objCtrl.AddItem IIf(rowDirection, wks.Cells(i, Spalte), wks.Cells(Spalte, i))
    Next

Set objCtrl = Nothing

End Function

 

Hallo Uwe, Hallo Exl121150,
erstmal herzlichen Dank für eure Mühe. Beide Modelle funktionieren perfekt. Mir ist natürlich klar, das mein Projekt wahrscheinlich weit weg von Perfekt ist, aber für uns hier ist es sehr hilfreich und vor allem es funktioniert. Eine Frage aber noch. Könnt ihr ohne viel Aufwand und für einen Anfänger verständlich sagen wie man so etwas besser macht. Es geht mir nicht um den Code, nur darum wie ich es in Zukunft besser machen kann. Mit der Zeit kann ich dann vielleicht mein Programm optimieren. Aber wie gesagt nochmals vielen Dank für die tolle Hilfe
Carsten

 

Hallo,

in der beiliegenden Datei habe ich dein Listbox-System vereinheitlicht, indem ich in den 3 Arbeitsblättern (Nummer, Modul, Info) die Listbox-Items, die zu einer Listbox gehören, in je eine strukturierte (=intelligente) Tabelle (tbLB1, tbLB2, tbLB3 und tbLB4) verfrachtet habe. Ich habe dir somit auch für die ListBox1 eine separate Tabelle tbLB1 eingefügt.

Deswegen habe ich dir auch eine neue Version der ListeFuellen1-Function erstellt mit einer geänderten Liste der formalen Parameter. Die Innereien dieser Function konnten damit vereinfacht werden. Damit in die ListBoxen am Ende keine Leer-Items eingefügt werden, fragt die Funktion vor dem Befüllen der betreffenden Listbox mittels Worksheetfunction.CountIf(..) ab, wie viele Einträge zu übertragen sind. Wenn man obige tbLB1..tbLB4-Tabellen in ein einziges Arbeitsblatt legte, könnte man sich in der ListeFuellen1-Funktion auch den ersten Parameter WsBez einsparen.

Den bisherigen VBA-Code, soweit er unverträglich bzw. obsolet wurde, habe ich in Kommentartext umgewandelt und ist somit größtenteils noch vorhanden.

Aber wie immer in der Programmiererei gilt der Grundsatz: Viele Wege führen nach Rom.
Somit gibt es sicher mehrere Varianten, die gleich sinnvoll sind. In der Regel sollte man aber ein einheitliches System vorsehen, um überflüssigen und unübersichtlichen Code zu vermeiden.

 

Hallo Exl121150,
vielen Dank für deine Mühe und die tolle Arbeit. Damit werde ich mich jetzt mal beschäftigen und auf jeden Fall habe ich viel gelernt womit ich jetzt auch andere Ideen die ich noch habe umsetzen und vereinfachen kann. Ich kann nur nochmals Danke sagen an alle die mich hier so toll unterstützt haben.
Carsten