Option Explicit
Dim x As Long
Dim FaName As String
Private Sub CommandButton1_Click()
FaName = ""
Unload Me
End Sub
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
FaName = ListBox1.Value
Sheets("Tabelle1").Range("D2").Value = FaName
Unload Me
End Sub
Private Sub TextBox1_Change()
Dim arr() As Variant
Dim index As Long, iCount As Long
x = IIf(IsEmpty(Range("B65536")), Range("B65536").End(xlUp).Row, 65536)
If TextBox1.Value = "" Then
ListBox1.RowSource = "B5:B" & x
Exit Sub
End If
ListBox1.RowSource = ""
ListBox1.Clear
For index = 5 To x
If LCase(Left(Cells(index, 2), Len(TextBox1))) = LCase(TextBox1) Then
If Sheets("Tabelle1").Cells(index, 2) <> "" Then
On Error Resume Next
ReDim Preserve arr(0, 0 To iCount)
arr(0, iCount) = Cells(index, 2)
iCount = iCount + 1
ListBox1.Column = arr
End If
End If
Next
End Sub
Private Sub UserForm_Initialize()
x = IIf(IsEmpty(Range("B65536")), Range("B65536").End(xlUp).Row, 65536)
ListBox1.RowSource = "B5:B" & x
End Sub
Wäre sehr dankbar für eine Lösung
Gruß Martin