ListBox does not include newly added lines to source table because of RowSource

49 views Asked by At

I have a UserForm UserForm1 with a ListBox ListBox1. The ListBox is filled with Data from the ListObject lstDaten. The Listbox can then be filtered by consultant cbBerater, product cbArtikel, country cbLand as well as a free text search via the textbox tbSuche.

On Initialisation the listbox is filled by using ListBox1.RowSource = lstDaten but when filtering via the Private Sub butAddItem_Click() I start with

ListBox1.RowSource = vbNullString 
ListBox1.Clear

to clear that out. I also tried ListBox1.RowSource = ""

Via another Userform a new line with data can be added to lstDaten

Here comes my problem: If I use any of the filtermethods, the new line is being shown in the listbox. But if none of the filters are applied, the new entry does not show up.

I think the problem might be with the .RowSource because if I delete that in the UserForm_Initialize it seems to work. But I don't understand why the listbox would not understand that there is a new line if i take out .RowSource

If after taking out .RowSource, put it back in, nothing changes. It still does not show the new line.

The RowSource has over 500 lines of Data. Filling it all with .RowSource when no filters apply is very fast. Adding every line indiviually works, but is a lot slower and really feels like it should be possible.

I hope you can help me find my error. Here is the butAddItem_Click(). If you need any other sub, please tell me.

Private Sub butAddItem_Click() 'Suche ausführen
Dim wsDaten As Worksheet: Set wsDaten = ThisWorkbook.Sheets("Daten")
Dim lstDaten As ListObject: Set lstDaten = wsDaten.ListObjects("Daten")
Dim NumZeilen As Integer: NumZeilen = lstDaten.ListRows.Count
Dim colSuche As Integer
Dim strDropdown As String
Dim arrHeader As String

ListBox1.RowSource = vbNullString 'Löschen der RowSource, damit neu gefüllt werden kann
ListBox1.Clear 'Falls noch Daten in Liste sind, werden diese entfernt

'Wenn durch alle gefiltert werden soll, festlegen des Starts und des Endes
If cbHeader.Value = "Alle" Then
    loopStart = 1
    loopEnde = lstDaten.ListColumns.Count
Else 'Wenn nach einer bestimten Spalte gefiltert werden soll, wird diese hier definiert
    loopStart = lstDaten.ListColumns(cbHeader.Value).Index
    loopEnde = loopStart
End If

For j = 1 To NumZeilen 'Loop durch die Zeilen zum Vergleich mit dem Suchtext

    'Wenn ein Artikel ausgewählt ist, überspringe Zeilen ohne diesen Artikel
    If Not cbArtikel = "Alle" And Not lstDaten.DataBodyRange(j, lstDaten.ListColumns("Artikel").Index) = cbArtikel Then
        GoTo NextIteration
    End If

    'Wenn ein Artikel ausgewählt ist, überspringe Zeilen ohne diesen Artikel
    If Not cbBerater = "Alle" And Not lstDaten.DataBodyRange(j, lstDaten.ListColumns("Berater").Index) = cbBerater Then
        GoTo NextIteration
    End If

    'Wenn ein Land ausgewählt ist, überspringe Zeilen ohne diesen Artikel
    If Not cbLand = "Alle" And Not lstDaten.DataBodyRange(j, lstDaten.ListColumns("Land").Index) = cbLand Then
        GoTo NextIteration
    End If

For k = loopStart To loopEnde 'Loop durch die Spalten
    If InStr(1, lstDaten.DataBodyRange(j, k), tbSuche.Value, vbTextCompare) > 0 Or tbSuche.Value = "" Then 'Vergleich Zelleninhalt mit Suchtext

        arrDaten = lstDaten.ListRows(j).Range 'Bei Übereinstimmung wird Zelleninhalt dem Array hinzugefügt.
        ListBox1.AddItem

        For i = 0 To 9 'Hinzufügen des Arrays zur Liste
            ListBox1.List(ListBox1.ListCount - 1, i) = arrDaten(1, i + 1)
        Next i
        GoTo NextIteration
    End If


Next k
NextIteration:
Next j

End Sub

PS: I know, parts of the code like GoTo are not optimal here, but it works :D

Thank you so much in advance.

0

There are 0 answers