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.