Need help by Timesheet

More Info...


There are 9 other modules and 5 UserForms

Part of the Sheet modules

Public tar As Range
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rg1 As Range, rg2 As Range, rg3 As Range, rg4 As Range, rg5 As Range, rg6 As Range, rg7 As Range
    Dim Cel As Range, i As Range, u As Range, code As Range, Flex As Range, Sick As Range, Vac As Range, Hol As Range
    Dim hh As Integer, mm As Integer
    Dim CV As Double
    Dim CVtxt As String, codeval As String
    Dim d As Date
    
    Set tar = Target
    
    Set rg1 = Range("D4:D18,D35:D50")
    Set rg2 = Range("E4:E18,E35:E52")
    Set rg3 = Range("F4:F18,F35:F50")
    Set rg4 = Range("G4:G18,G35:G50")
    Set rg5 = Range("H4:H18,H35:H50")
    Set rg6 = Range("I4:I18,I35:I50")
    Set rg7 = Range("C4:C18,C35:C50")
    
    Set u = Union(rg1, rg2, rg3, rg4, rg5, rg6, rg7)
    
    If (Application.Intersect(Target, u) Is Nothing) Then GoTo EndMacro
   
    Application.EnableEvents = False
    Application.CutCopyMode = False

    
    'For cells after the code column - format time values
  
rg1:
    'For columns before the code column - format time values
    Set i = Intersect(Target, rg1)
    If i Is Nothing Then GoTo rg2
    On Error GoTo rg2


    For Each Cel In i
      Set code = Cel.Offset(0, -1)
      Set Sick = code.Offset(, 8)
      Set Vac = code.Offset(, 9)
      Set Hol = code.Offset(, 10)
      
      If Not Cel.HasFormula And Len(Cel.Text) > 0 And Hol.Value = 8 Then
        MsgBox "You have Holiday Pay for this day and cannot take sick or vacation hours!"
        Cel.ClearContents
        GoTo rg2
      ElseIf Not Cel.HasFormula And Len(Cel.Text) > 0 Then
        CV = Cel.Value
        If Int(CV) - CV = 0 Then    'No Time entered or time was entered without colon
          If CV < 100 Then CV = CV * 100
          hh = Fix(CV / 100)
          mm = CV - (hh * 100)
          Cel = TimeValue(hh & ":" & mm)
        End If
        'Cel.NumberFormat = "[$-409]h:mm AM/PM;@"   '"h:mm AM/PM"
      End If
      
        With code
            codeval = .Value
            code_s = InStr(1, codeval, "s") Or InStr(1, codeval, "S")
            code_v = InStr(1, codeval, "v") Or InStr(1, codeval, "V")
        End With
        If Cel = "" Then
            If code_s > 0 Then
                Sick = ""
            ElseIf code_v > 0 Then
                Vac = ""
            ElseIf code_v > 0 And code_s > 0 Then
                Vac = ""
                Sick = ""
                GoTo rg2   'Make sure this is correct!!!!!!!!!
                
            End If
        Else

        If codeval = "" Then
                GoTo rg2   'Make sure this is correct!!!!!!!!!
                
        Else
        With code
            If code_s > 0 And code_v > 0 Then
                Sick = ""
                Vac = ""
                Hrs = SpecialHrs(code)
                If Hrs = "" Then
                 Application.EnableEvents = False
                    code.ClearContents
                 Application.EnableEvents = True
                Else
                    Sick = Hrs * 0.5
                    Vac = Hrs * 0.5
                End If
                GoTo rg2   'Make sure this is correct!!!!!!!!!
                
            End If

            If code_s > 0 Then
                Sick = ""
                SickHrs = SpecialHrs(code)
                    If SickHrs = "" Then
                 Application.EnableEvents = False
                    code.ClearContents
                 Application.EnableEvents = True
                    Else
                        Sick = SickHrs
                    End If
            End If
            
            If code_v > 0 Then
                Vac = ""
                VacHrs = SpecialHrs(code)
                    If VacHrs = "" Then
                 Application.EnableEvents = False
                    code.ClearContents
                 Application.EnableEvents = True
                    Else
                        Vac = VacHrs
                    End If
            End If
            
        End With
        End If
        End If
    Next Cel
    
rg2:
    'For columns before the code column - format time values
    Set i = Intersect(Target, rg2)
    If i Is Nothing Then GoTo rg3
    On Error GoTo rg3
   
    For Each Cel In i
      Set code = Cel.Offset(0, -2)
      Set Sick = code.Offset(, 8)
      Set Vac = code.Offset(, 9)
      Set Hol = code.Offset(, 10)
      
      If Not Cel.HasFormula And Len(Cel.Text) > 0 And Hol.Value = 8 Then
        MsgBox "You have Holiday Pay for this day and cannot take sick or vacation hours!"
        Cel.ClearContents
        GoTo rg3
      ElseIf Not Cel.HasFormula And Len(Cel.Text) > 0 Then
        CV = Cel.Value
        If Int(CV) - CV = 0 Then    'No Time entered or time was entered without colon
          If CV < 100 Then CV = CV * 100
            hh = Fix(CV / 100)
            mm = CV - (hh * 100)
          If CV >= 100 And CV < 700 Then
          Cel = hh & ":" & mm & " PM"
          Else
          Cel = hh & ":" & mm
          End If
          End If
        End If
        

        With code
            codeval = .Value
            code_s = InStr(1, codeval, "s") Or InStr(1, codeval, "S")
            code_v = InStr(1, codeval, "v") Or InStr(1, codeval, "V")
        End With
        
        If Cel = "" Then
            If code_s > 0 Then
                Sick = ""
            ElseIf code_v > 0 Then
                Vac = ""
            ElseIf code_v > 0 And code_s > 0 Then
                Vac = ""
                Sick = ""

                GoTo rg3   'Make sure this is correct!!!!!!!!!
                
            End If
        Else

        If codeval = "" Then
                GoTo rg3   'Make sure this is correct!!!!!!!!!
                
        Else
        With code
            If code_s > 0 And code_v > 0 Then
                Sick = ""
                Vac = ""
                Hrs = SpecialHrs(code)
                If Hrs = "" Then
                 Application.EnableEvents = False
                    code.ClearContents
                 Application.EnableEvents = True
                Else
                    Sick = Hrs * 0.5
                    Vac = Hrs * 0.5
                End If
                GoTo rg3   'Make sure this is correct!!!!!!!!!
                
            End If

            If code_s > 0 Then
                Sick = ""
                SickHrs = SpecialHrs(code)
                    If SickHrs = "" Then
                 Application.EnableEvents = False
                    code.ClearContents
                 Application.EnableEvents = True
                 Else
                        Sick = SickHrs
                    End If

            End If
            If code_v > 0 Then
                Vac = ""
                VacHrs = SpecialHrs(code)
                    If VacHrs = "" Then
                 Application.EnableEvents = False
                    code.ClearContents
                 Application.EnableEvents = True
                    Else
                        Vac = VacHrs
                    End If
            End If

        End With
        End If
        End If

    Next Cel

    
rg3:
    Set i = Intersect(Target, rg3)
    If i Is Nothing Then GoTo rg4
    On Error GoTo rg4
    
    For Each Cel In i
      Set code = Cel.Offset(0, -3)
      Set Sick = code.Offset(, 8)
      Set Vac = code.Offset(, 9)
      Set Hol = code.Offset(, 10)
      
      If Not Cel.HasFormula And Len(Cel.Text) > 0 And Hol.Value = 8 Then
        MsgBox "You have Holiday Pay for this day and cannot take sick or vacation hours!"
        Cel.ClearContents
        GoTo rg4
      ElseIf Not Cel.HasFormula And Len(Cel.Text) > 0 Then
        CV = Cel.Value
        If Int(CV) - CV = 0 Then    'No Time entered or time was entered without colon
          If CV < 100 Then CV = CV * 100
            hh = Fix(CV / 100)
            mm = CV - (hh * 100)
          If CV >= 800 And CV < 1200 Then
          Cel = hh & ":" & mm & " AM"
          Else
          Cel = hh & ":" & mm & " PM"
          End If
          End If
        End If
        
        With code
            codeval = .Value
            code_s = InStr(1, codeval, "s") Or InStr(1, codeval, "S")
            code_v = InStr(1, codeval, "v") Or InStr(1, codeval, "V")
        End With
        If Cel = "" Then
            If code_s > 0 Then
                Sick = ""
            ElseIf code_v > 0 Then
                Vac = ""
            ElseIf code_v > 0 And code_s > 0 Then
                Vac = ""
                Sick = ""
                GoTo rg4   'Make sure this is correct!!!!!!!!!
                
            End If
        Else

        If codeval = "" Then
                GoTo rg4   'Make sure this is correct!!!!!!!!!
                
        Else
        With code
            If code_s > 0 And code_v > 0 Then
                Sick = ""
                Vac = ""
                Hrs = SpecialHrs(code)
                If Hrs = "" Then
                 Application.EnableEvents = False
                    code.ClearContents
                 Application.EnableEvents = True
                Else
                    Sick = Hrs * 0.5
                    Vac = Hrs * 0.5
                End If
                GoTo rg4   'Make sure this is correct!!!!!!!!!
                
            End If
            If code_s > 0 Then
                Sick = ""
                SickHrs = SpecialHrs(code)
                    If SickHrs = "" Then
                 Application.EnableEvents = False
                    code.ClearContents
                 Application.EnableEvents = True
                    Else
                        Sick = SickHrs
                    End If

            End If
            If code_v > 0 Then
                Vac = ""
                VacHrs = SpecialHrs(code)
                    If VacHrs = "" Then
                 Application.EnableEvents = False
                    code.ClearContents
                 Application.EnableEvents = True
                    Else
                        Vac = VacHrs
                    End If
            End If

        End With
        End If
        End If
        
    Next Cel