AW: ComboBox auf Tabelle
21.12.2022 13:17:46
Rudi
Hallo,
das geht so nicht.
Nimm eine Textbox zur Eingabe des Suchbegriffs und eine Listbox für die Ausgabe der Treffer
Code für Textbox:
Private Sub TextBox1_Change()
With ListBox1
.ColumnCount = 2
.List = GetList(TextBox1)
End With
End Sub
In ein Modul:
Function GetList(strMatch As String)
Dim objROW As Object, oObj
Dim arrIN, arrOUT(), i As Long
Set objROW = CreateObject("scripting.dictionary")
strMatch = "*" & LCase(strMatch) & "*"
With Sheets("Lager-Bestand")
arrIN = .Range(.Cells(2, 3), .Cells(Rows.Count, 4).End(xlUp)).Value
End With
For i = 1 To UBound(arrIN)
If LCase(arrIN(i, 1)) Like strMatch Or LCase(arrIN(i, 2)) Like strMatch Then
objROW(i) = 0
End If
Next i
If objROW.Count Then
i = 0
ReDim arrOUT(1 To objROW.Count, 1 To 2)
For Each oObj In objROW
i = i + 1
arrOUT(i, 1) = arrIN(oObj, 1)
arrOUT(i, 2) = arrIN(oObj, 2)
Next oObj
Else
ReDim arrOUT(1 To 1, 1 To 2)
arrOUT(1, 1) = "NoMatch"
End If
GetList = arrOUT
End Function
Alles ungetestet und optimierbar.
Gruß
Rudi