Dim wksCombo As Worksheet, Zeile1 As Long
Private Sub ComboBox1_Click()
Dim Zeile As Long
Zeile = Zeile1 + Me.ComboBox1.ListIndex
Me.TextBox2 = wksCombo.Cells(Zeile, 3).Value
Me.TextBox3 = wksCombo.Cells(Zeile, 4).Value
'u.s.w. für weitere Textboxen
End Sub
Private Sub CommandButton2_Click()
Dim Zelle As Long
With Worksheets("Rapport")
Zelle = .Cells(Rows.Count, 2).End(xlUp).Row + 1
.Cells(Zelle, 2) = TextBox1
.Cells(Zelle, 3) = ComboBox1
.Cells(Zelle, 4) = fncSchicht
.Cells(Zelle, 5) = TextBox2
.Cells(Zelle, 6) = TextBox3
.Cells(Zelle, 7) = TextBox4
.Cells(Zelle, 8) = TextBox5
.Cells(Zelle, 9) = TextBox6
.Cells(Zelle, 10) = TextBox7
.Cells(Zelle, 11) = TextBox8
.Cells(Zelle, 12) = TextBox9
.Cells(Zelle, 13) = TextBox10
.Cells(Zelle, 14) = TextBox14
.Cells(Zelle, 15) = TextBox12
.Cells(Zelle, 16) = TextBox13
.Cells(Zelle, 17) = TextBox14
.Cells(Zelle, 18) = TextBox15
.Cells(Zelle, 19) = TextBox16
.Cells(Zelle, 20) = TextBox17
.Cells(Zelle, 21) = TextBox18
.Cells(Zelle, 22) = TextBox19
.Cells(Zelle, 23) = TextBox20
.Cells(Zelle, 24) = TextBox21
.Cells(Zelle, 25) = TextBox22
.Cells(Zelle, 26) = TextBox23
End With
End Sub
Private Sub UserForm_Initialize()
Dim rngSorte As Range
Set wksCombo = Worksheets("Combobox")
'Sorten in Spalte B inkl. Anzeige Spalte C der Combobox1 als Auswahl zuweisen
With wksCombo
Set rngSorte = .Range(.Cells(3, 2), .Cells(.Rows.Count, 2).End(xlUp).Offset(0, 1))
Zeile1 = rngSorte.Row '1. Zeile mit Sorteneintrag
Me.ComboBox1.RowSource = "'" & .Name & "'!" & rngSorte.Address
End With
'Konfigurieren Combobox
With Me.ComboBox1
.ColumnCount = 2
.ListWidth = 200
.ColumnWidths = "50Pt;140Pt"
End With
TextBox1 = Date
End Sub
Private Function fncSchicht()
Dim bytSchicht As Byte
Dim arrSchicht
arrSchicht = Array("nichts gewählt", "Früh", "Mittag", "Nacht")
bytSchicht = -OptionButton1 - 2 * OptionButton2 - 3 * OptionButton3
fncSchicht = arrSchicht(bytSchicht)
End Function
Private Sub ComboBox1_Change()
Dim Zeile As Long
Zeile = Zeile1 + Me.ComboBox1.ListIndex
Me.TextBox2 = wksCombo.Cells(Zeile, 2).Value
Me.TextBox3 = wksCombo.Cells(Zeile, 3).Value
Me.TextBox4 = wksCombo.Cells(Zeile, 4).Value
Me.TextBox5 = wksCombo.Cells(Zeile, 5).Value
Me.TextBox6 = wksCombo.Cells(Zeile, 6).Value
End Sub
Private Sub ComboBox2_Change()
Dim Zeile As Long
Zeile = Zeile2 + Me.ComboBox2.ListIndex
Me.TextBox17 = wksCombo.Cells(Zeile, 2).Value
End Sub
Private Sub CommandButton2_Click()
Dim Zelle As Long
With Worksheets("Rapport")
Zelle = .Cells(Rows.Count, 2).End(xlUp).Row + 1
.Cells(Zelle, 2) = TextBox1
.Cells(Zelle, 3) = ComboBox1
.Cells(Zelle, 4) = fncSchicht
.Cells(Zelle, 5) = TextBox2
.Cells(Zelle, 6) = TextBox3
.Cells(Zelle, 7) = TextBox4
.Cells(Zelle, 8) = TextBox5
.Cells(Zelle, 9) = TextBox6
.Cells(Zelle, 10) = TextBox7
.Cells(Zelle, 11) = TextBox8
.Cells(Zelle, 12) = TextBox9
.Cells(Zelle, 13) = TextBox10
.Cells(Zelle, 14) = TextBox14
.Cells(Zelle, 15) = TextBox12
.Cells(Zelle, 16) = TextBox13
.Cells(Zelle, 17) = TextBox14
.Cells(Zelle, 18) = TextBox15
.Cells(Zelle, 19) = TextBox16
.Cells(Zelle, 20) = TextBox17
.Cells(Zelle, 21) = TextBox18
.Cells(Zelle, 22) = TextBox19
.Cells(Zelle, 23) = TextBox20
.Cells(Zelle, 24) = TextBox21
.Cells(Zelle, 25) = TextBox22
.Cells(Zelle, 26) = TextBox23
End With
End Sub
Private Sub CommandButton3_Click()
Dim Tb As Integer
On Error Resume Next
For Tb = 2 To 26
Me.Controls("TextBox" & Tb) = ""
Next Tb
End Sub
Private Sub UserForm_Initialize()
Dim rngSorte As Range
Set wksCombo = Worksheets("Combobox")
With wksCombo
Set rngSorte = .Range(.Cells(3, 2), .Cells(.Rows.Count, 2).End(xlUp).Offset(0, 1))
Zeile1 = rngSorte.Row
Me.ComboBox1.RowSource = "'" & .Name & "'!" & rngSorte.Address
Set rngSorte = .Range(.Cells(3, 8), .Cells(.Rows.Count, 5).End(xlUp).Offset(0, 2))
Zeile2 = rngSorte.Row
Me.ComboBox2.RowSource = "'" & .Name & "'!" & rngSorte.Address
End With
With Me.ComboBox1
.ColumnCount = 2
.ListWidth = 200
.ColumnWidths = "50Pt;140Pt"
End With
With Me.ComboBox2
.ColumnCount = 2
.ListWidth = 200
.ColumnWidths = "50Pt;140Pt"
End With
TextBox1 = Date
End Sub
Private Function fncSchicht()
Dim bytSchicht As Byte
Dim arrSchicht
arrSchicht = Array("nichts gewählt", "Früh", "Mittag", "Nacht")
bytSchicht = -OptionButton1 - 2 * OptionButton2 - 3 * OptionButton3
fncSchicht = arrSchicht(bytSchicht)
End Function
Private Sub CommandButton1_Click()
Rapport.Hide
End Sub
Private Sub UserForm_Initialize()
Dim rngSorte As Range
Set wksCombo = Worksheets("Combobox")
With wksCombo
'Sorten-Auswahl für Combobox1 einlesen
Set rngSorte = .Range(.Cells(3, 2), .Cells(.Rows.Count, 2).End(xlUp).Offset(0, 1))
Zeile1 = rngSorte.Row
Me.ComboBox1.RowSource = "'" & .Name & "'!" & rngSorte.Address
'Code-Auswahl für Combobox2 bis 4 einlesen
Set rngSorte = .Range(.Cells(2, 10), .Cells(.Rows.Count, 10).End(xlUp).Offset(0, 1))
Me.ComboBox2.RowSource = "'" & .Name & "'!" & rngSorte.Address
Me.ComboBox3.RowSource = "'" & .Name & "'!" & rngSorte.Address
Me.ComboBox4.RowSource = "'" & .Name & "'!" & rngSorte.Address
End With
With Me.ComboBox1
.ColumnCount = 2
.ListWidth = 200
.ColumnWidths = "50Pt;140Pt"
End With
'Combobox 2 bi4 configurieren - Diese Einstellungen kann man auch direkt _
unter den Eigenschaften der Comboboxen machen
With Me.ComboBox2
.ColumnCount = 2
.ListWidth = 150
.ColumnWidths = "50Pt;100Pt"
End With
With Me.ComboBox3
.ColumnCount = 2
.ListWidth = 150
.ColumnWidths = "50Pt;100Pt"
End With
With Me.ComboBox4
.ColumnCount = 2
.ListWidth = 150
.ColumnWidths = "50Pt;100Pt"
End With
TextBox1 = Date
End Sub
Private Sub ComboBox2_Click() 'ähnlich dann für Box 3 und 4
With Me.ComboBox2
Me.TextBox17 = .List(.ListIndex, 1)
End With
End Sub
Sub tt()
With Application.CommandBars("Ply")
.Reset
.Enabled = True
End With
End Sub