title: VBA duplicate value (not using data validation)
body: <p>I have a question regarding catching a user for entering duplicate values in a sheet. We are unable to use data validation because cut/copy/paste throws out the data validation and allows them to enter the dupe value. I was originally using this code:</p>
  <pre><code>Option Explicit
  
  Public Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  
  '******problem when copying entire row and pasting into new row, enables user to paste dupe Box ID #******
  
  'Defining variables in Mailroom
  Dim WS As Worksheet, EvalRange As Range
         
  'Range to check for duplicates
  Set EvalRange = Worksheets(&quot;Mailroom&quot;).Range(&quot;Box_ID_Number&quot;)
        
  'Checking if entered value is in the defined range; also if cell is empty exit macro
  If Intersect(Target, EvalRange) Is Nothing Or Target.Cells.Count &gt; 1 Then Exit Sub
  If IsEmpty(Target) Then Exit Sub
  
  'If user enters dupe value in specified range then error message pops up and event is undone
  If WorksheetFunction.CountIf(EvalRange, Target.Value) &gt; 1 Then
      MsgBox Target.Value &amp; &quot; already appears as a Box ID Number. Please enter a unique ID.&quot;
      Application.EnableEvents = False
      Application.Undo
      Application.EnableEvents = True
  End If
  
  End Sub
  </code></pre>
  <p>The code works fine for keeping a user from typing a dupe value in the column for &quot;Box ID Number.&quot; The problem I am having is that if the user is to copy a Box ID Number from a column and another cell from a different column, they are able to paste a Dupe Value that the <code>_SheetChange</code> does not catch.  When we were first creating the code for this we were disabling cut/copy/paste functions; however, others using the sheet apparently still need that function for other portions of the sheet.</p>
  <p>Any ideas?</p>