Option Explicit

Public wpText As String 'Klartext für Wartungen

Public tbform As String 'Stunden TextBox Formatierung
Public stdform As String 'Stunden Kürzel Formatierung
Public tbstart As String 'Starttext für Stunden TextBox
Public tbdeci As Integer 'Teiler für Stunden Nachkommastelle

Public spmin As Integer 'SpinButton min Wert
Public spmax As Integer 'SpinButton max Wert
Public spchange As Integer 'SpinButton change Wert
Public spdelay As Integer 'SpinButton delay Wert

Public tbcolor As String 'Farbe für inaktive TextBox

Public mname1 As String 'Mitarbeiter Name 1
Public mname2 As String 'Mitarbeiter Name 2
Public mname3 As String 'Mitarbeiter Name 3
Public mname4 As String 'Mitarbeiter Name 4
Public mname5 As String 'Mitarbeiter Name 5

' --------------------------------------------------------------- Bericht erstellen --------------------------------------------------------------------
Private Sub CommandButton1_Click()

    If MsgBox("Möchten Sie diesen Bericht wirklich erstellen?", vbYesNo) = vbYes Then

    Dim n As Long

    ' ///// Art des Berichts exportieren ////////////////////////////////
        Worksheets("Vorlage").Cells(6, 1).Value = ComboBox1.Value
    ' __________________________________________________________________

    ' ///// Kunden Daten exportieren ////////////////////////////////////
        Worksheets("Vorlage").Cells(9, 5).Value = UserForm1.Controls("TextBox" & 1).Value
        Worksheets("Vorlage").Cells(10, 5).Value = UserForm1.Controls("TextBox" & 2).Value
        Worksheets("Vorlage").Cells(11, 5).Value = UserForm1.Controls("TextBox" & 3).Value
        Worksheets("Vorlage").Cells(13, 5).Value = UserForm1.Controls("TextBox" & 4).Value
    ' __________________________________________________________________

    ' ///// Mitarbeiter exportieren /////////////////////////////////////
        For n = 2 To 6
            Worksheets("Vorlage").Cells(n + 7, 14).Value = UserForm1.Controls("ComboBox" & n).Value
        Next n
    ' __________________________________________________________________


    ' ///// Datum exportieren ///////////////////////////////////////////
        Dim stdDrive As String
        Dim stdWork As String
        For n = 1 To 5
            stdDrive = UserForm1.Controls("TextBox" & (n + 4)).Value
            stdWork = UserForm1.Controls("TextBox" & (n + 9)).Value
            If stdDrive = "0,00" And stdWork = "0,00" Then
                Worksheets("Vorlage").Cells(n + 17, 1).Value = ""
                Else: Worksheets("Vorlage").Cells(n + 17, 1).Value = UserForm1.Controls("DTPicker" & n).Value
            End If
        Next n
    ' __________________________________________________________________


    ' ///// Übernachtung exportieren ////////////////////////////////////
        For n = 1 To 5
            If UserForm1.Controls("CheckBox" & n).Value = True Then
                    Worksheets("Vorlage").Cells(n + 17, 10) = "X"
                Else: Worksheets("Vorlage").Cells(n + 17, 10) = ""
            End If
        Next n
    ' __________________________________________________________________

    ' ///// Fahr- / Arbeitszeit exportieren /////////////////////////////
        Dim gesSum As String
        gesSum = 0

        For n = 5 To 9
            Dim ges As String
            Dim tBox As String

            tBox = UserForm1.Controls("TextBox" & n).Value
            If tBox <> "0,00" Then
                    Worksheets("Vorlage").Cells(n + 13, 13).Value = tBox + stdform
                Else: Worksheets("Vorlage").Cells(n + 13, 13).Value = ""
            End If

            tBox = UserForm1.Controls("TextBox" & n + 5).Value
            If tBox <> "0,00" Then
                    Worksheets("Vorlage").Cells(n + 13, 15).Value = tBox + stdform
                Else: Worksheets("Vorlage").Cells(n + 13, 15).Value = ""
            End If

            ges = CDbl(UserForm1.Controls("TextBox" & n)) + CDbl(UserForm1.Controls("TextBox" & n + 5))
            If ges <> 0 Then
                    Worksheets("Vorlage").Cells(n + 13, 17).Value = Format(ges, tbform) & stdform
                Else: Worksheets("Vorlage").Cells(n + 13, 17).Value = ""
            End If

            gesSum = CDbl(gesSum) + CDbl(ges) ' Gesamt Zeit errechnen

        Next n
    ' __________________________________________________________________

    ' ///// Mitarbeiter Zählen //////////////////////////////////////////
        Dim maCount As Integer
        Dim stdGes As String
        maCount = 0

        For n = 9 To 13
            If Worksheets("Vorlage").Cells(n, 14).Value <> "" Then
                maCount = maCount + 1
            End If
        Next n
        stdGes = CDbl(gesSum) * CDbl(maCount)
    ' __________________________________________________________________

    ' ///// Gesamt Zeit exportieren /////////////////////////////////////
        If ComboBox1.ListIndex <> 0 Then
                Worksheets("Vorlage").Cells(24, 17).Value = Format(gesSum, tbform) & stdform
                Worksheets("Vorlage").Cells(25, 17).Value = Format(stdGes, tbform) & stdform
            Else
                Worksheets("Vorlage").Cells(24, 17).Value = wpText
                Worksheets("Vorlage").Cells(25, 17).Value = wpText
        End If
    ' __________________________________________________________________

    ' ///// Ausgeführte Arbeiten exportieren ////////////////////////////
        Worksheets("Vorlage").Cells(29, 1).Value = TextBox15.Value
    ' __________________________________________________________________

    ' ///// Unterschriftsfelder exportieren /////////////////////////////
        Worksheets("Vorlage").Cells(64, 1).Value = ComboBox2.Value
        Worksheets("Vorlage").Cells(64, 14).Value = TextBox4.Value
        Worksheets("Vorlage").Cells(65, 1).Value = Date
        Worksheets("Vorlage").Cells(65, 14).Value = Date
    ' __________________________________________________________________

    End If

End Sub

Private Sub SpinButton1_Change()

    TextBox5.Text = (SpinButton1.Value / tbdeci)
    TextBox5.Value = Format(TextBox5, tbform)

    If TextBox5.Text = tbstart Then
        TextBox5.ForeColor = tbcolor
    Else
        TextBox5.ForeColor = 0
    End If
End Sub
Private Sub SpinButton2_Change()

    TextBox6.Text = (SpinButton2.Value / tbdeci)
    TextBox6.Value = Format(TextBox6, tbform)

    If TextBox6.Text = tbstart Then
        TextBox6.ForeColor = tbcolor
    Else
        TextBox6.ForeColor = 0
    End If
End Sub
Private Sub SpinButton3_Change()

    TextBox7.Text = (SpinButton3.Value / tbdeci)
    TextBox7.Value = Format(TextBox7, tbform)

    If TextBox7.Text = tbstart Then
        TextBox7.ForeColor = tbcolor
    Else
        TextBox7.ForeColor = 0
    End If
End Sub
Private Sub SpinButton4_Change()

    TextBox8.Text = (SpinButton4.Value / tbdeci)
    TextBox8.Value = Format(TextBox8, tbform)

    If TextBox8.Text = tbstart Then
        TextBox8.ForeColor = tbcolor
    Else
        TextBox8.ForeColor = 0
    End If
End Sub
Private Sub SpinButton5_Change()

    TextBox9.Text = (SpinButton5.Value / tbdeci)
    TextBox9.Value = Format(TextBox9, tbform)

    If TextBox9.Text = tbstart Then
        TextBox9.ForeColor = tbcolor
    Else
        TextBox9.ForeColor = 0
    End If
End Sub
Private Sub SpinButton6_Change()

    TextBox10.Text = (SpinButton6.Value / tbdeci)
    TextBox10.Value = Format(TextBox10, tbform)

    If TextBox10.Text = tbstart Then
        TextBox10.ForeColor = tbcolor
    Else
        TextBox10.ForeColor = 0
    End If
End Sub
Private Sub SpinButton7_Change()

    TextBox11.Text = (SpinButton7.Value / tbdeci)
    TextBox11.Value = Format(TextBox11, tbform)

    If TextBox11.Text = tbstart Then
        TextBox11.ForeColor = tbcolor
    Else
        TextBox11.ForeColor = 0
    End If
End Sub
Private Sub SpinButton8_Change()

    TextBox12.Text = (SpinButton8.Value / tbdeci)
    TextBox12.Value = Format(TextBox12, tbform)

    If TextBox12.Text = tbstart Then
        TextBox12.ForeColor = tbcolor
    Else
        TextBox12.ForeColor = 0
    End If
End Sub
Private Sub SpinButton9_Change()

    TextBox13.Text = (SpinButton9.Value / tbdeci)
    TextBox13.Value = Format(TextBox13, tbform)

    If TextBox13.Text = tbstart Then
        TextBox13.ForeColor = tbcolor
    Else
        TextBox13.ForeColor = 0
    End If
End Sub
Private Sub SpinButton10_Change()

    TextBox14.Text = (SpinButton10.Value / tbdeci)
    TextBox14.Value = Format(TextBox14, tbform)

    If TextBox14.Text = tbstart Then
        TextBox14.ForeColor = tbcolor
    Else
        TextBox14.ForeColor = 0
    End If
End Sub

Private Sub UserForm_Initialize()

    ' --------------------------------------------------------------- Variabeln deklarieren --------------------------------------------------------------------
    wpText = "Wartungspauschale" 'Klartext für Wartungen"

    stdform = " std" 'Stunden Kürzel Formatierung
    tbform = "#0.00" 'Stunden TextBox Formatierung
    tbdeci = 2 'Teiler für Stunden Nachkommastelle
    tbstart = "0,00" 'Starttext für Stunden TextBox
    spmin = 0 'SpinButton min Wert
    spmax = 32 'SpinButton max Wert
    spchange = 1 'SpinButton change Wert
    spdelay = 80 'SpinButton delay Wert
    tbcolor = RGB(210, 210, 210) 'Farbe für inaktive TextBox

    mname1 = "Ralf Pries" 'Mitarbeiter Name 1
    mname2 = "Henning Meckel" 'Mitarbeiter Name 2
    mname3 = "Florian Skotz" 'Mitarbeiter Name 3
    'mname4 = "HIER NAME EINFÜGEN" 'Mitarbeiter Name 4
    'mname5 = "HIER NAME EINFÜGEN" 'Mitarbeiter Name 5

    Dim n As Long
    For n = 5 To 14
    UserForm1.Controls("TextBox" & n).Text = tbstart
    UserForm1.Controls("TextBox" & n).Locked = True
    UserForm1.Controls("TextBox" & n).ForeColor = tbcolor
    Next n

    For n = 1 To 10
    UserForm1.Controls("SpinButton" & n).Min = spmin
    UserForm1.Controls("SpinButton" & n).Max = spmax
    UserForm1.Controls("SpinButton" & n).SmallChange = spchange
    UserForm1.Controls("SpinButton" & n).Delay = spdelay
    Next n


    ' --------------------------------------------------------------- Art des Berichts füllen --------------------------------------------------------------------
    With UserForm1.ComboBox1
        .AddItem "W A R T U N G S B E R I C H T"
        .AddItem "S E R V I C E B E R I C H T"
        .AddItem "R E P E R A T U R B E R I C H T"
        .ListIndex = 1
    End With

    ' --------------------------------------------------------------- Kunden Daten Laden --------------------------------------------------------------------
    For n = 1 To 4
        UserForm1.Controls("TextBox" & n).Value = Worksheets("kdDaten").Cells(n + 1, 2).Value
    Next n

    ' --------------------------------------------------------------- Namensfelder füllen --------------------------------------------------------------------
    For n = 2 To 6
        With UserForm1.Controls("ComboBox" & n)
            .AddItem ""
            If mname1 <> "" Then .AddItem mname1
            If mname2 <> "" Then .AddItem mname2
            If mname3 <> "" Then .AddItem mname3
            If mname4 <> "" Then .AddItem mname4
            If mname5 <> "" Then .AddItem mname5
        End With
    Next n


End Sub