AW: Sucherergebnisse in Listbox richtig sortieren
08.11.2019 13:31:09
Werner
Hallo Stefan,
so:
Option Explicit
Private Sub CommandButton1_Click() 'Suche und Ergebnisse in Listbox
Dim rngCell As Range
Dim strFirstAddress As String
With Worksheets("Kunden").Range("Kunden") 'Bereich im Kunden-Tab ggf. erweitern
Me.ListBox1.Clear
Set rngCell = .Find(what:=TextBox1, LookIn:=xlValues, lookat:=xlPart, MatchCase:=False)
If Not rngCell Is Nothing Then
strFirstAddress = rngCell.Address
Do
With Me.ListBox1
.ColumnCount = 5
.AddItem
.List(.ListCount - 1, 0) = Worksheets("Kunden").Cells(rngCell.Row, 1)
.List(.ListCount - 1, 1) = Worksheets("Kunden").Cells(rngCell.Row, 2)
.List(.ListCount - 1, 2) = Worksheets("Kunden").Cells(rngCell.Row, 3)
.List(.ListCount - 1, 3) = Worksheets("Kunden").Cells(rngCell.Row, 4)
.List(.ListCount - 1, 4) = Worksheets("Kunden").Cells(rngCell.Row, 5)
.ColumnWidths = "2,0cm;2,0cm;2,5cm;1,5cm;1,5cm"
End With
Set rngCell = .FindNext(rngCell)
Loop While Not rngCell Is Nothing And rngCell.Address strFirstAddress
Else
MsgBox "Kunde nicht gefunden. Alternativ mit * eingeben", 48
End If
End With
End Sub
Gruß Werner