Ich suche ein code mit dem ich in einer Combobox eine Sorte aus wellen kann
Und es mir dann die dafür aus gewellten TextBox die Daten an zeigt
Für Hilfe Schon mal Danke schön
https://www.herber.de/bbs/user/69368.xls
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