Ich möchte gerne auf meinem Userform das tabellenblatt mit Hilfe einer Optionsfeld aus wellen können in dem die eingegebene Daten eingetragen werben Solen
Gruss Fredy
https://www.herber.de/bbs/user/71778.xlsm
Private Sub CommandButton1_Click()
Dim Zelle As Long
Dim i As Long
Dim leer As Long
Dim Zeile As Long
With Worksheets("Altpapier")
i = .Range("B:B").Find(what:=ComboBox1.Value).Row
For leer = i To 200000
If .Cells(i, 2) = "" Then
'Zelle = .Cells(Rows.Count, 2).End(xlUp).Row + 1
.Rows(i).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
.Cells(i, 2) = TextBox1
.Cells(i, 3) = ComboBox1
.Cells(i, 4) = fncSchicht
.Cells(i, 5) = TextBox4
.Cells(i, 6) = TextBox5
.Cells(i, 7) = TextBox6
.Cells(i, 8) = TextBox7
.Cells(i, 9) = TextBox8
.Cells(i, 10) = TextBox9
.Cells(i, 11) = TextBox10
.Cells(i, 12) = TextBox11
.Cells(i, 13) = TextBox12
.Cells(i, 14) = TextBox13
.Cells(i, 15) = TextBox14
.Cells(i, 16) = TextBox15
.Cells(i, 17) = TextBox16
.Cells(i, 18) = TextBox17
.Cells(i, 19) = TextBox18
.Cells(i, 20) = TextBox19
.Cells(i, 21) = TextBox20
.Cells(i, 22) = TextBox21
.Cells(i, 23) = TextBox22
.Cells(i, 24) = TextBox23
.Cells(i, 25) = TextBox24
.Cells(i, 26) = TextBox25
.Cells(i, 27) = TextBox26
.Cells(i, 28) = TextBox27
GoTo liste
Exit Sub
End If
i = i + 1
Next
End With
GoTo liste
liste:
For Zeile = 3 To Worksheets("Altpapier").Cells(Rows.Count, 2).End(xlUp).Row
Worksheets("Altpapier").Cells(Zeile, 1) = Worksheets("Altpapier").Cells(Zeile, 1).Row - 2
Next
End Sub
Private Sub CommandButton2_Click()
Checkliste.Hide
End Sub
Private Sub CommandButton3_Click()
Dim Tb As Integer
On Error Resume Next
For Tb = 4 To 27
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(2, 1), .Cells(.Rows.Count, 1).End(xlUp).Offset(0, 1))
Zeile1 = rngSorte.Row
Me.ComboBox1.RowSource = "'" & .Name & "'!" & rngSorte.Address
End With
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 CommandButton1_Click()
Dim Zelle As Long
Dim i As Long
Dim leer As Long
Dim Zeile As Long
With Worksheets("Altpapier")
i = .Range("B:B").Find(what:=ComboBox1.Value).Row
For leer = i To 200000
If .Cells(i, 2) = "" Then
'Zelle = .Cells(Rows.Count, 2).End(xlUp).Row + 1
.Rows(i).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
.Cells(i, 2) = TextBox1
.Cells(i, 3) = ComboBox1
.Cells(i, 4) = fncSchicht
.Cells(i, 5) = TextBox4
.Cells(i, 6) = TextBox5
.Cells(i, 7) = TextBox6
.Cells(i, 8) = TextBox7
.Cells(i, 9) = TextBox8
.Cells(i, 10) = TextBox9
.Cells(i, 11) = TextBox10
.Cells(i, 12) = TextBox11
.Cells(i, 13) = TextBox12
.Cells(i, 14) = TextBox13
.Cells(i, 15) = TextBox14
.Cells(i, 16) = TextBox15
.Cells(i, 17) = TextBox16
.Cells(i, 18) = TextBox17
.Cells(i, 19) = TextBox18
.Cells(i, 20) = TextBox19
.Cells(i, 21) = TextBox20
.Cells(i, 22) = TextBox21
.Cells(i, 23) = TextBox22
.Cells(i, 24) = TextBox23
.Cells(i, 25) = TextBox24
.Cells(i, 26) = TextBox25
.Cells(i, 27) = TextBox26
.Cells(i, 28) = TextBox27
GoTo liste
Exit Sub
End If
i = i + 1
Next
End With
GoTo liste
liste:
For Zeile = 3 To Worksheets("Altpapier").Cells(Rows.Count, 2).End(xlUp).Row
Worksheets("Altpapier").Cells(Zeile, 1) = Worksheets("Altpapier").Cells(Zeile, 1).Row - 2
Next
End Sub
Private Sub CommandButton2_Click()
Checkliste.Hide
End Sub
Private Sub CommandButton3_Click()
Dim Tb As Integer
On Error Resume Next
For Tb = 4 To 27
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(2, 1), .Cells(.Rows.Count, 1).End(xlUp).Offset(0, 1))
Zeile1 = rngSorte.Row
Me.ComboBox1.RowSource = "'" & .Name & "'!" & rngSorte.Address
End With
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 CommandButton1_Click()
Dim Zelle As Long
Dim i As Long
Dim leer As Long
Dim Zeile As Long
With Worksheets("ActiveSheet")
i = .Range("B:B").Find(what:=ComboBox1.Value).Row
For leer = i To 200000
If .Cells(i, 2) = "" Then
'Zelle = .Cells(Rows.Count, 2).End(xlUp).Row + 1
.Rows(i).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
.Cells(i, 2) = TextBox1
.Cells(i, 3) = ComboBox1
.Cells(i, 4) = fncSchicht
.Cells(i, 5) = TextBox4
.Cells(i, 6) = TextBox5
.Cells(i, 7) = TextBox6
.Cells(i, 8) = TextBox7
.Cells(i, 9) = TextBox8
.Cells(i, 10) = TextBox9
.Cells(i, 11) = TextBox10
.Cells(i, 12) = TextBox11
.Cells(i, 13) = TextBox12
.Cells(i, 14) = TextBox13
.Cells(i, 15) = TextBox14
.Cells(i, 16) = TextBox15
.Cells(i, 17) = TextBox16
.Cells(i, 18) = TextBox17
.Cells(i, 19) = TextBox18
.Cells(i, 20) = TextBox19
.Cells(i, 21) = TextBox20
.Cells(i, 22) = TextBox21
.Cells(i, 23) = TextBox22
.Cells(i, 24) = TextBox23
.Cells(i, 25) = TextBox24
.Cells(i, 26) = TextBox25
.Cells(i, 27) = TextBox26
.Cells(i, 28) = TextBox27
GoTo liste
Exit Sub
End If
i = i + 1
Next
End With
GoTo liste
liste:
For Zeile = 3 To Worksheets("ActiveSheet").Cells(Rows.Count, 2).End(xlUp).Row
Worksheets("ActiveSheet").Cells(Zeile, 1) = Worksheets("ActiveSheet").Cells(Zeile, 1).Row - 2
Next
End Sub
Private Sub CommandButton2_Click()
Checkliste.Hide
End Sub
Private Sub CommandButton3_Click()
Dim Tb As Integer
On Error Resume Next
For Tb = 4 To 27
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(2, 1), .Cells(.Rows.Count, 1).End(xlUp).Offset(0, 1))
Zeile1 = rngSorte.Row
Me.ComboBox1.RowSource = "'" & .Name & "'!" & rngSorte.Address
End With
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 OptionButton4_Click()
Worksheets("Altpapier").Activate
End Sub
Private Sub OptionButton5_Click()
Worksheets("Zellstoff").Activate
End Sub