When Testing, VBA code works, When run normally, It doesn't

I am struggling with trying to understand why when I test the code that searches for duplicates works in test mode, but doesn't work when I run it normally. Can someone possibly tell me where I am going wrong please?

I have a sub routine called FindDuplicatesInColumn, and I placed it in the section that Validates data entered into a user form.

```' Check if all data has been entered on the userform
Function Data_Validation() As Boolean 'Declare Function with Bool as data type

'Default True. False if any conditions met. When a function is called, a new variable,
'with the function name and datatype given is created.  You'll set the value in the
'function.  When the function ends either in Exit Function or
'End Function, whatever is contained in this variable is returned as the Functions result
    Data_Validation = True
' Check if all data has been entered on the userform

    

     If ARLArea = False And KNBArea = False And LSQArea = False And RSQArea = False And RevenueControlInspectors = False And SpecialRequirementTeam = False Then
        MsgBox "Select Area!", vbInformation, ("Area")
        ARLArea.SetFocus
        Data_Validation = False
        Exit Function
        End If
     If EmployeeNo1 = "" Then
        MsgBox "Enter Employee Number!", vbInformation, ("Employee Number")
        EmployeeNo1.SetFocus
        Data_Validation = False
        Exit Function
        End If
     If FirstName1 = "" Then
        MsgBox "Enter First Name!", vbInformation, ("First Name")
        FirstName1.SetFocus
        Data_Validation = False
        Exit Function
        End If
     If LastName1 = "" Then
        MsgBox "Enter Last Name!", vbInformation, ("Last Name")
        LastName1.SetFocus
        Data_Validation = False
        Exit Function
        End If
     If CSA2 = False And CSA1 = False And CSS2 = False And CSS1 = False And CSM2 = False And CSM1 = False And AM = False And RCI = False And SRT = False Then
        MsgBox "Select Grade!", vbInformation, ("Grade")
        CSA2.SetFocus
        Data_Validation = False
        Exit Function
        End If
    
     BlnVal = 1
 
     FindDuplicatesInColumn

End Function```

The Sub routine I created is this

```Sub FindDuplicatesInColumn()
Dim sAccNum As String
Dim rAccLst As Range
Dim res
Dim IdVal As Integer


    

Set rAccLst = Sheets("Data Input").Range("B5:B" & Range("B" & Rows.Count).End(xlUp).Row) 


sAccNum = EmployeeNo1

If Not rAccLst.Find(What:=sAccNum, LookIn:=xlValues, Lookat:=xlPart) Is Nothing Then
MsgBox "Sorry, This person already exists in the Database!"

 'Empty Area
        ARLArea = False
        LSQArea = False
        KNBArea = False
        RSQArea = False
        RevenueControlInspectors = False
        SpecialRequirementTeam = False

    'Empty EmployeeNo1
        EmployeeNo1.Value = ""

    'Empty FirstName1
        FirstName1.Value = ""

    'Empty LastName1
        LastName1.Value = ""
    
    'Empty Grade
        CSA2 = False
        CSA1 = False
        CSS2 = False
        CSS1 = False
        CSM2 = False
        CSM1 = False
        AM = False
        RCI = False
        SRT = False
    
        sAccNum = ""
 If Data_Validation() = False Then
        Exit Sub
 End If

 Else
       'Sheets("Data Input").Range("B" & Range("B" & Rows.Count).End(xlUp).Row).Offset(1, 0).Value = "ACC" & sAccNum
 End If



End Sub```

Maybe I am doing something wrong, or using the wrong code which is causing it to skip the duplicates search. I am trying to get the search to check when the user enters the EmployeeNo1 in the user form, it is checked against the data in Column B of Data_Input to see if it is duplicated. If it is, then to stop the data from the form being added to the database.