habe das mal so abgeändert wie du vorgeschlagen hast.
Bekomme die Meldung: Laufzeitfehler 9 .... Index außerhalb des gültigen Bereichs
Code:
Sub eintragenZ(targetSheet As String, dtDate As Date, iZ1 As Integer, iZ2 As Integer, iZ3 As Integer, iZ4 As Integer, iZ5 As Integer, iZ6 As Integer, iZZ As Integer, Optional iCol As Integer = 0)
'
' Schaltfläche4_Klicken Makro
Dim lfdNr As Long
With Sheets(targetSheet)
lfdNr = .Cells(Rows.Count, 1 + iCol).End(xlUp).Row
'lfdNr = .Cells(Rows.Count, 1).End(xlUp).Row
.Cells(lfdNr + 1, 1 + iCol).NumberFormat = "m/d/yyyy"
.Cells(lfdNr + 1, 1 + iCol).Value = CDate(dtDate)
.Cells(lfdNr + 1, 2 + iCol) = Format(CStr(dtDate), "dddd")
.Cells(lfdNr + 1, 3 + iCol) = .Cells(lfdNr, 3) + 1
.Cells(lfdNr + 1, 4 + iCol) = iZ1
.Cells(lfdNr + 1, 5 + iCol) = iZ2
.Cells(lfdNr + 1, 6 + iCol) = iZ3
.Cells(lfdNr + 1, 7 + iCol) = iZ4
.Cells(lfdNr + 1, 8 + iCol) = iZ5
.Cells(lfdNr + 1, 9 + iCol) = iZ6
.Cells(lfdNr + 1, 10 + iCol) = iZZ
End With
End Sub
Code:
Private Sub cbSaveZ_Click()
Dim ctrl As Control, bCheck As Boolean
bCheck = True
For Each ctrl In Me.Controls
If TypeName(ctrl) = "TextBox" Then
If ctrl = "" Then
ctrl.BackColor = vbYellow
bCheck = False
Else
ctrl.BackColor = vbWhite
End If
End If
Next
If bCheck = False Then
MsgBox "Fehlende Werte"
Exit Sub
End If
Dim sSheet As String
If tbDatum <> "" Then
If IsDate(tbDatum) Then
Select Case WorksheetFunction.Weekday(CDate(tbDatum), 2)
Case 6
sSheet = "Samstag"
Case 3
sSheet = "Mittwoch"
Case Else
tbDatum.SetFocus
MsgBox "Datum kein Mittwoch oder Samstag"
Exit Sub
End Select
eintragenZ sSheet & "sziehung", tbDatum, tb1, tb2, tb3, tb4, tb5, tb6, tbSZ
eintragenZ "Lotto-Uhr-" & sSheet, tbDatum, tb1, tb2, tb3, tb4, tb5, tb6, tbSZ, Columns("U") - 1
eintragenZ "Kreuz-Tipp-" & sSheet, tbDatum, tb1, tb2, tb3, tb4, tb5, tb6, tbSZ, Columns("U") - 1
'Den Muenztipp gibt's nur Samstag?
If sSheet = "Samstag" Then eintragenZ "Münztipp-Samstag", tbDatum, tb1, tb2, tb3, tb4, tb5, tb6, tbSZ, Columns("U") - 1
'eintragenZ sSheet, tbDatum, tb1, tb2, tb3, tb4, tb5, tb6, tbSZ
'eintragenZ "Lotto-Uhr-Samstag", tbDatum, tb1, tb2, tb3, tb4, tb5, tb6, tbSZ, Columns("Q").Column - 1
'eintragenZ "Lotto-Uhr-Mittwoch", tbDatum, tb1, tb2, tb3, tb4, tb5, tb6, tbSZ, Columns("Q").Column - 1
'eintragenZ Kreuz - Tipp - Samstag, tbDatum, tb1, tb2, tb3, tb4, tb5, tb6, tbSZ, Columns("U") - 1
'eintragenZ Kreuz - Tipp - Mittwoch, tbDatum, tb1, tb2, tb3, tb4, tb5, tb6, tbSZ, Columns("U") - 1
'eintragenZ Münztipp - Samstag, tbDatum, tb1, tb2, tb3, tb4, tb5, tb6, tbSZ, Columns("U") - 1
For Each ctrl In Me.Controls
If TypeName(ctrl) = "TextBox" Then ctrl = ""
Next
MsgBox "Ziehung eingetragen"
Else
MsgBox "kein Datum"
End If
Else
MsgBox "Datum fehlt"
End If
End Sub