AW: UF zwei Listboxen und zwei Tabellen
02.07.2018 16:55:21
Peter
Hallo Nepumuk,
besten Dank für Deine Hilfe - funktioniert aber leider nicht.
Es wird eine völlig andere Zeile ausgewählt.
Anbei die gesamten Codes in UF:
Option Explicit
Private Sub CommandButton3_Click()
Tag = "1"
With Worksheets("ScantabelleKopierer1Ber")
.Cells(ListBox1.ListIndex + 1, 1).Value = CDbl(TextBox1.Value)
.Cells(ListBox1.ListIndex + 1, 2).Value = TextBox2.Value
.Cells(ListBox1.ListIndex + 1, 3).Value = TextBox3.Value
.Cells(ListBox1.ListIndex + 1, 4).Value = TextBox4.Value
.Cells(ListBox1.ListIndex + 1, 5).Value = CDbl(TextBox5.Value)
.Cells(ListBox1.ListIndex + 1, 6).Value = CDbl(TextBox6.Value)
End With
With Worksheets("ScantabelleKopierer2Ber")
.Cells(ListBox2.ListIndex + 1, 1).Value = CDbl(TextBox7.Value)
.Cells(ListBox2.ListIndex + 1, 2).Value = TextBox8.Value
.Cells(ListBox2.ListIndex + 1, 3).Value = TextBox9.Value
.Cells(ListBox2.ListIndex + 1, 4).Value = TextBox10.Value
.Cells(ListBox2.ListIndex + 1, 5).Value = CDbl(TextBox11.Value)
.Cells(ListBox2.ListIndex + 1, 6).Value = CDbl(TextBox12.Value)
End With
Tag = vbNullString
End Sub
'
Private Sub CommandButton3_Click()
'Worksheets("ScantabelleKopierer1Ber").Activate
'With Worksheets("ScantabelleKopierer1Ber")
'With Range("ScantabelleKopierer1Ber!A2:F501")
' Me.Tag = "1"
' .Cells(ListBox1.ListIndex + 1, 1).Value = CDbl(TextBox1.Value)
' .Cells(ListBox1.ListIndex + 1, 2).Value = (TextBox2.Value)
' .Cells(ListBox1.ListIndex + 1, 3).Value = (TextBox3.Value)
' .Cells(ListBox1.ListIndex + 1, 4).Value = (TextBox4.Value)
' .Cells(ListBox1.ListIndex + 1, 5).Value = CDbl(TextBox5.Value)
' .Cells(ListBox1.ListIndex + 1, 6).Value = CDbl(TextBox6.Value)
' Me.Tag = ""
'End With
' End With
'End Sub
Private Sub CommandButton5_Click()
Worksheets("ScantabelleKopierer2Ber").Activate
With Worksheets("ScantabelleKopierer2Ber")
With Range("ScantabelleKopierer2Ber!A2:F501")
Me.Tag = "1"
.Cells(ListBox2.ListIndex + 1, 1).Value = CDbl(TextBox7.Value)
.Cells(ListBox2.ListIndex + 1, 2).Value = (TextBox8.Value)
.Cells(ListBox2.ListIndex + 1, 3).Value = (TextBox9.Value)
.Cells(ListBox2.ListIndex + 1, 4).Value = (TextBox10.Value)
.Cells(ListBox2.ListIndex + 1, 5).Value = CDbl(TextBox11.Value)
.Cells(ListBox2.ListIndex + 1, 6).Value = CDbl(TextBox12.Value)
Me.Tag = ""
End With
End With
End Sub
Private Sub CommandButton6_Click()
Me.CommandButton3 = True
Me.CommandButton5 = True
End Sub
Private Sub CommandButton4_Click()
Dim Zeile%
Zeile = Cells(Rows.Count, 2).End(xlUp).Row
ListBox1.ListIndex = Zeile - 1
ListBox2.ListIndex = Zeile - 1
Me.TextBox2 = "5056i"
Me.TextBox8 = "8056i"
End Sub
Private Sub ListBox1_Click()
If Me.Tag = "1" Then Exit Sub
'klick = ListBox1.ListIndex
With ListBox1
Me.TextBox1 = .List(.ListIndex, 0)
Me.TextBox2 = .List(.ListIndex, 1)
Me.TextBox3 = .List(.ListIndex, 2)
Me.TextBox4 = .List(.ListIndex, 3)
Me.TextBox5 = .List(.ListIndex, 4)
Me.TextBox6 = .List(.ListIndex, 5)
End With
ListBox2.ListIndex = ListBox1.ListIndex
End Sub
Private Sub ListBox2_Click()
If Me.Tag = "1" Then Exit Sub
'klick = ListBox1.ListIndex
With ListBox2
Me.TextBox7 = .List(.ListIndex, 0)
Me.TextBox8 = .List(.ListIndex, 1)
Me.TextBox9 = .List(.ListIndex, 2)
Me.TextBox10 = .List(.ListIndex, 3)
Me.TextBox11 = .List(.ListIndex, 4)
Me.TextBox12 = .List(.ListIndex, 5)
End With
End Sub
Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TextBox9.Value = TextBox3.Value
End Sub
Private Sub TextBox4_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TextBox10.Value = TextBox4.Value
End Sub
'damit die Reihenfolge stimmt bei Änderung muss eine Hilfspalte mit 1,2, bis.... existieren
Private Sub userform_initialize()
Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet
Dim Zeile%
Zeile = Cells(Rows.Count, 2).End(xlUp).Row
With Worksheets("ScantabelleKopierer2Ber")
With ListBox2
.RowSource = "ScantabelleKopierer2Ber!A2:F501"
.ColumnCount = 6
.ColumnWidths = "0,8cm;1,3cm;1,2cm;5,5cm;1,5cm;1,5cm"
.ColumnHeads = False
End With
End With
ListBox2.ListIndex = Zeile - 1
With Worksheets("ScantabelleKopierer1Ber")
With ListBox1
.RowSource = "ScantabelleKopierer1Ber!A2:F501"
.ColumnCount = 6
.ColumnWidths = "0,8cm;1,3cm;1,2cm;5,5cm;1,5cm;1,5cm"
.ColumnHeads = False
End With
End With
ListBox1.ListIndex = Zeile - 1
End Sub
Vielleicht erleichtert es Dir die Arbeit.
Besten Dank
Gruss Peter