|
Private Sub UserForm_Initialize()
Dim Zeile As Long
'Schleife über alle Zeilen der Tabelle
For Zeile = 1 To Tabelle3.Cells(Rows.Count, 2).End(xlUp).Row
Me.ListBox1.AddItem Tabelle3.Cells(Zeile, 1)
Me.ListBox1.List(Me.ListBox1.ListCount - 1, 1) = Tabelle3.Cells(Zeile, 2)
Me.ListBox1.List(Me.ListBox1.ListCount - 1, 2) = Tabelle3.Cells(Zeile, 3)
Me.ListBox1.List(Me.ListBox1.ListCount - 1, 3) = Tabelle3.Cells(Zeile, 4)
Me.ListBox1.List(Me.ListBox1.ListCount - 1, 4) = Tabelle3.Cells(Zeile, 5)
Me.ListBox1.List(Me.ListBox1.ListCount - 1, 5) = Tabelle3.Cells(Zeile, 6)
Me.ListBox1.List(Me.ListBox1.ListCount - 1, 6) = CInt(Tabelle3.Cells(Zeile, 7))
Next Zeile
'Erstes Element als Default auswählen
Me.ListBox1.Selected(0) = True
End Sub
Private Sub TextBox1_Change()
Dim Zeile As Long
Me.ListBox1.Clear
'Schleife über alle Zeilen der Tabelle
For Zeile = 1 To Tabelle3.Cells(Rows.Count, 2).End(xlUp).Row
If InStr(1, LCase(Tabelle3.Cells(Zeile, 1).Value), LCase(Me.TextBox1.Value)) <> 0 Or _
InStr(1, LCase(Tabelle3.Cells(Zeile, 2).Value), LCase(Me.TextBox1.Value)) <> 0 Or _
InStr(1, LCase(Tabelle3.Cells(Zeile, 3).Value), LCase(Me.TextBox1.Value)) <> 0 Or _
InStr(1, LCase(Tabelle3.Cells(Zeile, 4).Value), LCase(Me.TextBox1.Value)) <> 0 Or _
InStr(1, LCase(Tabelle3.Cells(Zeile, 5).Value), LCase(Me.TextBox1.Value)) <> 0 Or _
InStr(1, LCase(Tabelle3.Cells(Zeile, 6).Value), LCase(Me.TextBox1.Value)) <> 0 Then
Me.ListBox1.AddItem Tabelle3.Cells(Zeile, 1)
Me.ListBox1.List(Me.ListBox1.ListCount - 1, 1) = Tabelle3.Cells(Zeile, 2)
Me.ListBox1.List(Me.ListBox1.ListCount - 1, 2) = Tabelle3.Cells(Zeile, 3)
Me.ListBox1.List(Me.ListBox1.ListCount - 1, 3) = Tabelle3.Cells(Zeile, 4)
Me.ListBox1.List(Me.ListBox1.ListCount - 1, 4) = Tabelle3.Cells(Zeile, 5)
Me.ListBox1.List(Me.ListBox1.ListCount - 1, 5) = Tabelle3.Cells(Zeile, 6)
Me.ListBox1.List(Me.ListBox1.ListCount - 1, 6) = CInt(Tabelle3.Cells(Zeile, 7))
End If
Next Zeile
End Sub
?
|