AW: Wortsuche im Userform (Listbox)
14.11.2015 17:45:07
Nepumuk
Hallo,
teste mal:
Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 13 Then
Dim s As String
Dim Found As Range
Dim FirstAddress As String
Dim lngRow As Long, lngColumn As Long, lngFoundRow As Long
Dim avntValues()
' On Error Resume Next
s = Trim(TextBox1.Text) 'Sucheingabe über Textbox1 steuern
If s = "" Then Exit Sub
ListBox1.Clear
With Tabelle4
Set Found = Tabelle4.Cells.Find(what:=s, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)
If Not Found Is Nothing Then
FirstAddress = Found.Address
ListBox1.ColumnCount = 40 'Gibt die Werte der gefundenen Treffer an (Spaltenbezogen)
Do
lngFoundRow = Found.Row
Redim Preserve avntValues(1 To 40, 0 To lngRow)
For lngColumn = 1 To 40
avntValues(lngColumn, lngRow) = Tabelle4.Cells(lngFoundRow, lngColumn).Value
Next
Set Found = Tabelle4.Cells.FindNext(after:=Found)
If Found.Address = FirstAddress Then Exit Do
lngRow = lngRow + 1
Loop
ListBox1.Column = avntValues
End If
End With
End If
End Sub
Gruß
Nepumuk