How do I prevent duplicate entries across sheets

[kmdavidson]

I have searched and searched but haven't come up with an answer to this:

Does anyone know how to prevent duplicate entries when using more than one sheet. e.g. I want to set it up so that I can only use a number once. So if the number was already entered on sheet 2 and I try to enter it on sheet 1, I get an error message. I know it has to do with data validation and defining a range, but I can't get the syntax and instructions right. Thanks.

I have been using Microsoft's Knowledge Base Article 213185 as a model.

 

[erik.van.geit]

(Mrexcel was inaccessible to me for some time, what's happening ?)
I had about the same code as DRJ but added
Target.Activate (before ELSE)
when you want to type another number you don't have te click again in the cell

regards,
Erik

 

[DRJ]

(Mrexcel was inaccessible to me for some time, what's happening ?)

same thing happens to me every day for a few minutes. I guess we are not supposed to know what is going on during that time.

 

[jhonty4]

Hi,
Been trying to use the same code but "Subscript out of range pops up " . Only difference in mine is that the range to be validated is sheet3, B18:b217 , to be checked from sheet2, b18:217 . The code i am using is :

Private Sub Worksheet_Change(ByVal Target As Range)


If Not Target.Column = 2 Then Exit Sub 'Only work on column B


    If Application.WorksheetFunction.CountIf(Sheets("Sheet3").Range("$B$18:$B$217"), Target.Value) + Application.WorksheetFunction.CountIf(Sheets("Sheet2").Range("$B$18:$B$218"), Target.Value) > 1 Then
        MsgBox "You have entered a duplicate number, please try again"
        Target.ClearContents
    Else
End If


End Sub

 

[MYTURBOMR2]

Hello All, I am new to Macros and all this, I am wondering however if there is a way to create a code that ranges across 8 worksheets and Columns / Rows A40 to W40?