|
Hi
ok hier der komplette Code
aber weiterhin bei den Eigenschaften der Listbox die RowSource entfernen
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;0"
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)"
ListBox1.ColumnCount = 2
ListBox1.ColumnWidths = "75;0"
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)
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) = 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
|