Prevent data entry in cell if data already exist in workbook based on value of column A&B

slandy20 

You are asking a spreadsheet to do the work of a database and application, but here goes.

If you are already using VBA code in your workbook, you should already be aware of the security risk involved.

You did not mention the approximate number of employees that may appear on any of these spreadsheets. I wrote VBA code that assumes a maximum of 250, resulting in a last possible row of employee data of 275. I've made that number adjustable from one place.

This technique does not prevent data entry to an inappropriate daily cell, but it does immediately react to an entry there, rejecting the entry, and notifying the user why.

So each of the (four) worksheets that contain a timesheet will require a Worksheet_Change event handler. The code in each will be identical, including a call to one of two procedures in a standard module; most of the work is done there.

Worksheet_Change code:

    Dim rngRangeOfInterest  As Range
    Dim rngModifiedCells    As Range    '...with time data
    
    '----
    Set rngRangeOfInterest = Me.Range("C" & in2FIRST_ROW_OF_EMPEE_TIMES _
            & ":" & strLAST_COL_OF_EMPEE_TIMES & in2LAST_ROW_OF_EMPEE_TIMES)
    Set rngModifiedCells = Intersect(Target, rngRangeOfInterest)
    If rngModifiedCells Is Nothing Then
        GoTo NextChangesOfInterest
    End If
    Call RejectInappropriateTimeEntry(Me, rngModifiedCells)
NextChangesOfInterest:
    '   [If there are more types of changes to be handled - currently or
    '   in the future - include code for them here.]

Standard module code:

    '====   PROJECT-LEVEL CONSTANTS
    Public Const in2FIRST_ROW_OF_EMPEE_TIMES As Integer = 26
    Public Const in2LAST_ROW_OF_EMPEE_TIMES As Integer = 275
    Public Const strLAST_COL_OF_EMPEE_TIMES = "ND"


Public Function CheckWorksheetForEmpeeHrs(ByVal pstrWorksheetName As String _
        , ByVal pin4EmpeeID As Long, ByVal pstrCheckColumn As String _
        ) As String

    Dim strReturnValue      As String
    
    Dim wsComparisonSheet   As Worksheet
    Dim rngEmpeeIDCell  As Range
    Dim strCellAddress  As String
    Dim strRowNum       As String   '(don't need to convert to number)
    Dim strCellValue    As String
    Dim strEmpeeName    As String
    
    '----
    Set wsComparisonSheet = Sheets(pstrWorksheetName)
    With wsComparisonSheet
        Set rngEmpeeIDCell = .Range("A" _
                & in2FIRST_ROW_OF_EMPEE_TIMES & ":A" _
                & in2LAST_ROW_OF_EMPEE_TIMES).Find( _
                pin4EmpeeID)
        If rngEmpeeIDCell Is Nothing Then
            '...the employee is not present on this worksheet.
            strReturnValue = "OK"
            GoTo CheckWSForEmpeeHrs_Exit
        End If
        strCellAddress = rngEmpeeIDCell.Address(False, False)
        strRowNum = Mid$(strCellAddress, 2)
        strCellValue = .Range(pstrCheckColumn & strRowNum).Value & ""
        If strCellValue = "" Then
            strReturnValue = "OK"
            GoTo CheckWSForEmpeeHrs_Exit
        ElseIf StrComp(strCellValue, "SH", vbTextCompare) = 0 Then
            strReturnValue = "OK"
            GoTo CheckWSForEmpeeHrs_Exit
        Else
            strEmpeeName = .Range("B" & strRowNum).Value & ""
            strReturnValue = "A value is already present in sheet " _
                    & pstrWorksheetName & " for " & strEmpeeName _
                    & ": " & strCellValue
        End If
    End With
CheckWSForEmpeeHrs_Exit:
    CheckWorksheetForEmpeeHrs = strReturnValue
    Exit Function

End Function


Public Sub RejectInappropriateTimeEntry(ByVal pwsEditedSheet As Worksheet _
        , ByRef prngModifiedCells As Range _
        )

    Dim in2Milepost As Integer  'for debugging
    Dim rngModifiedCell As Range
    Dim strModCellAddress   As String
    Dim in4PosnOfDollarSign As Long
    Dim strModCellColumn    As String
    Dim strModCellRow       As String   '(don't need to convert it to number)
    Dim strNewValue     As String
    Dim rngEmpeeIDCellA As Range    '...in the edited sheet
    Dim in4EmpeeID      As Long
    Dim strResult   As String   'result of check for one cell
    
    '----
    On Error GoTo RejectIDE_ErrHndlr
    For Each rngModifiedCell In prngModifiedCells
        '  --   Grab the new value and check it for special values.
        in2Milepost = 100
        strNewValue = rngModifiedCell.Value & ""
        in2Milepost = 110
        If strNewValue = "" Then
            GoTo NextModifiedCell
        ElseIf StrComp(strNewValue, "SH", vbTextCompare) = 0 Then
            'Per business rules, this is OK.
            GoTo NextModifiedCell
            ' => Consistency checks are not included.
        End If
        '  --   Gather information from the modified cell's address.
        in2Milepost = 150
        strModCellAddress = rngModifiedCell.Address(True, False)
        in2Milepost = 160
        in4PosnOfDollarSign = InStr(2, strModCellAddress, "$")
        strModCellColumn = Left$(strModCellAddress, in4PosnOfDollarSign - 1)
        in2Milepost = 170
        strModCellRow = Mid$(strModCellAddress, in4PosnOfDollarSign + 1)
        '  --   Get the Employee ID for this row.
        in2Milepost = 190
        Set rngEmpeeIDCellA = pwsEditedSheet.Range("A" & strModCellRow)
        in4EmpeeID = rngEmpeeIDCellA.Value
        '  --   In very similar blocks of code, check the sheets for already-
        '       present data for that person & date among other departments.
        '       If such data found, "erase" the new data and issue a message.
        in2Milepost = 200
        If pwsEditedSheet.Name <> "Dept1" Then
            strResult = CheckWorksheetForEmpeeHrs("Dept1" _
                    , in4EmpeeID, strModCellColumn)
            If strResult <> "OK" Then
                Application.EnableEvents = False
                rngModifiedCell.Value = Empty
                Application.EnableEvents = True
                '
                Call MsgBox(strResult & vbCrLf & vbCrLf & "Your input was removed." _
                        , vbExclamation Or vbOKOnly _
                        , "Inappropriate Data Entry for Time")
                GoTo NextModifiedCell
            End If
        End If
        in2Milepost = 220
        If pwsEditedSheet.Name <> "Dept2" Then
            strResult = CheckWorksheetForEmpeeHrs("Dept2" _
                    , in4EmpeeID, strModCellColumn)
            If strResult <> "OK" Then
                Application.EnableEvents = False
                rngModifiedCell.Value = Empty
                Application.EnableEvents = True
                '
                Call MsgBox(strResult & vbCrLf & vbCrLf & "Your input was removed." _
                        , vbExclamation Or vbOKOnly _
                        , "Inappropriate Data Entry for Time")
                GoTo NextModifiedCell
            End If
        End If
        in2Milepost = 240
        If pwsEditedSheet.Name <> "Dept3" Then
            strResult = CheckWorksheetForEmpeeHrs("Dept3" _
                    , in4EmpeeID, strModCellColumn)
            If strResult <> "OK" Then
                Application.EnableEvents = False
                rngModifiedCell.Value = Empty
                Application.EnableEvents = True
                '
                Call MsgBox(strResult & vbCrLf & vbCrLf & "Your input was removed." _
                        , vbExclamation Or vbOKOnly _
                        , "Inappropriate Data Entry for Time")
                GoTo NextModifiedCell
            End If
        End If
        in2Milepost = 260
        If pwsEditedSheet.Name <> "Dept4" Then
            strResult = CheckWorksheetForEmpeeHrs("Dept4" _
                    , in4EmpeeID, strModCellColumn)
            If strResult <> "OK" Then
                Application.EnableEvents = False
                rngModifiedCell.Value = Empty
                Application.EnableEvents = True
                '
                Call MsgBox(strResult & vbCrLf & vbCrLf & "Your input was removed." _
                        , vbExclamation Or vbOKOnly _
                        , "Inappropriate Data Entry for Time")
                GoTo NextModifiedCell
            End If
        End If
NextModifiedCell:
    Next rngModifiedCell
    
RejectIDE_Exit:
    Exit Sub

RejectIDE_ErrHndlr:
    Dim in4ErrorCode    As Long
    Dim strErrorDescr   As String
    Dim strMessage      As String
    '  --   Capture data about the processing error.
    in4ErrorCode = Err.Number
    strErrorDescr = Err.Description
    '  --   Inform the user.
    strMessage = "Error " & in4ErrorCode & ":" & vbCrLf _
            & strErrorDescr & vbCrLf & vbCrLf & in2Milepost
    Call MsgBox(strMessage, vbExclamation Or vbOKOnly _
            , "RejectInappropriateTimeEntry")
    '  --
    If in2Milepost >= 200 Then
        Resume NextModifiedCell
    Else
        Resume RejectIDE_Exit
    End If
End Sub

Of course, you would use different worksheet name literals in procedure RejectInappropriateTimeEntry.

And the code can easily be defeated. I'll assume this is to catch unintentionally inappropriate entries.