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)
If IsNull(ListBox1.Value) Then
MsgBox "Sie haben nichts ausgewählt!"
Exit Sub
Else
FaName = ListBox1.Value
Sheets("UB").Range("K15").Value = FaName
Unload Me
End If
End Sub
Private Sub TextBox1_Change()
Dim arr() As Variant
Dim index As Long, iCount As Long
x = IIf(IsEmpty(Range("U65536")), Range("U65536").End(xlUp).Row, 65536)
If TextBox1.Value = "" Then
ListBox1.RowSource = "U8:U" & x
Exit Sub
End If
ListBox1.RowSource = ""
ListBox1.Clear
For index = 5 To x
If LCase(Left(Cells(index, 21), Len(TextBox1))) = LCase(TextBox1) Then
If Sheets("UB").Cells(index, 21) <> "" Then
On Error Resume Next
ReDim Preserve arr(2, 0 To iCount)
arr(0, iCount) = Cells(index, 21)
arr(1, iCount) = Cells(index, 22)
arr(2, iCount) = Cells(index, 22)
iCount = iCount + 1
ListBox1.Column = arr
End If
End If
Next
On Error GoTo 0
End Sub
Private Sub UserForm_Initialize()
x = IIf(IsEmpty(Range("U65536")), Range("U65536").End(xlUp).Row, 65536)
ListBox1.RowSource = "U8:W" & x
End Sub