Hi
ich habe es jetzt mal so geändert
erstelle dir eine weitere Combobox2 auf der Maske, da du den Status ja sicher auch ändern willst
in Combobox1 kannst du dann in der Listbox nach dem Staus filtern
Private Sub ComboBox1_Click()
Dim lngLetzte As Long
Dim lngAnz As Long
Dim lngZeile As Long
If ComboBox1.ListIndex = -1 Then Exit Sub
ListBox1.ColumnCount = 2
ListBox1.ColumnWidths = "75;5"
With Sheets("0_Stammd_FLA")
lngLetzte = .Cells(.Rows.Count, 3).End(xlUp).Row
ListBox1.Clear
For lngZeile = 2 To lngLetzte
If .Cells(lngZeile, 21).Value = ComboBox1.Text Then
ListBox1.AddItem Cells(lngZeile, 3).Value
lngAnz = ListBox1.ListCount
ListBox1.List(lngAnz - 1, 1) = lngZeile
End If
Next lngZeile
End With
End Sub
Private Sub UserForm_Activate()
Call FillListBox
End Sub
Sub FillListBox()
Dim lngLetzte As Long
Dim lngAnz As Long
Dim lngZeile As Long
ComboBox1.AddItem "bereit für Zulassungsprüfung (FL MT)"
ComboBox1.AddItem "bereit für die Zulassung (FL MT)"
ComboBox2.AddItem "bereit für Zulassungsprüfung (FL MT)"
ComboBox2.AddItem "bereit für die Zulassung (FL MT)"
ListBox1.ColumnCount = 2
ListBox1.ColumnWidths = "75;5"
With Sheets("0_Stammd_FLA")
lngLetzte = .Cells(.Rows.Count, 3).End(xlUp).Row
ListBox1.Clear
For lngZeile = 2 To lngLetzte
ListBox1.AddItem Cells(lngZeile, 3).Value
lngAnz = ListBox1.ListCount
ListBox1.List(lngAnz - 1, 1) = lngZeile
Next lngZeile
End With
End Sub
Private Sub ListBox1_Click()
Dim Spalte As Integer, zeile As Long
Spalte = 3
zeile = ListBox1.List(ListBox1.ListIndex, 1)
With Worksheets(1)
ComboBox2.Text = .Cells(zeile, Spalte + 18)
Me.TextBox3 = .Cells(zeile, Spalte)
Me.TextBox4 = .Cells(zeile, Spalte + 1)
Me.TextBox5 = .Cells(zeile, Spalte + 2)
Me.TextBox6 = .Cells(zeile, Spalte + 3)
Me.TextBox7 = .Cells(zeile, Spalte + 4)
Me.TextBox8 = .Cells(zeile, Spalte + 5)
Me.TextBox9 = .Cells(zeile, Spalte + 6)
Me.TextBox10 = .Cells(zeile, Spalte + 7)
Me.TextBox11 = .Cells(zeile, Spalte + 8)
Me.TextBox14 = .Cells(zeile, Spalte + 11)
Me.TextBox17 = .Cells(zeile, Spalte + 14)
Me.TextBox18 = .Cells(zeile, Spalte + 15)
Me.TextBox19 = .Cells(zeile, Spalte + 16)
Me.TextBox30 = .Cells(zeile, Spalte + 27)
Me.TextBox32 = .Cells(zeile, Spalte + 29)
Me.TextBox33 = .Cells(zeile, Spalte + 30)
Me.TextBox34 = .Cells(zeile, Spalte + 31)
Me.TextBox35 = .Cells(zeile, Spalte + 32)
Me.TextBox36 = .Cells(zeile, Spalte + 33)
Me.TextBox37 = .Cells(zeile, Spalte + 34)
Me.TextBox38 = .Cells(zeile, Spalte + 35)
Me.TextBox39 = .Cells(zeile, Spalte + 36)
Me.TextBox40 = .Cells(zeile, Spalte + 37)
Me.TextBox41 = .Cells(zeile, Spalte + 38)
Me.TextBox42 = .Cells(zeile, Spalte + 39)
Me.TextBox43 = .Cells(zeile, Spalte + 40)
Me.TextBox44 = .Cells(zeile, Spalte + 41)
End With
End Sub
Private Sub CommandButton1_Click()
Dim Spalte As Integer, zeile As Long
Spalte = 3
zeile = ListBox1.List(ListBox1.ListIndex, 1)
With Worksheets(1)
.Cells(zeile, Spalte + 18) = ComboBox2.Text
.Cells(zeile, Spalte) = Me.TextBox3
.Cells(zeile, Spalte + 1) = Me.TextBox4
.Cells(zeile, Spalte + 2) = Me.TextBox5
.Cells(zeile, Spalte + 3) = Me.TextBox6
.Cells(zeile, Spalte + 4) = Me.TextBox7
.Cells(zeile, Spalte + 5) = Me.TextBox8
.Cells(zeile, Spalte + 6) = Me.TextBox9
.Cells(zeile, Spalte + 7) = Me.TextBox10
.Cells(zeile, Spalte + 8) = Me.TextBox11
.Cells(zeile, Spalte + 11) = Me.TextBox14
.Cells(zeile, Spalte + 14) = Me.TextBox17
.Cells(zeile, Spalte + 15) = Me.TextBox18
.Cells(zeile, Spalte + 16) = Me.TextBox19
.Cells(zeile, Spalte + 27) = Me.TextBox30
.Cells(zeile, Spalte + 29) = Me.TextBox32
.Cells(zeile, Spalte + 30) = Me.TextBox33
.Cells(zeile, Spalte + 31) = Me.TextBox34
.Cells(zeile, Spalte + 32) = Me.TextBox35
.Cells(zeile, Spalte + 33) = Me.TextBox36
.Cells(zeile, Spalte + 34) = Me.TextBox37
.Cells(zeile, Spalte + 35) = Me.TextBox38
.Cells(zeile, Spalte + 36) = Me.TextBox39
.Cells(zeile, Spalte + 37) = Me.TextBox40
.Cells(zeile, Spalte + 38) = Me.TextBox41
.Cells(zeile, Spalte + 39) = Me.TextBox42
.Cells(zeile, Spalte + 40) = Me.TextBox43
.Cells(zeile, Spalte + 41) = Me.TextBox44
End With
End Sub
Private Sub CommandButton2_Click()
Unload UserForm1
End Sub
MfG Tom
|