VBA Prevent Duplicate Entry

[Nico Learning]

Hi,

Ive created a Spreadsheet which has name running along B1 to J1

and dates running down A1 to A180

Now i have Name1 is in B1, Name2 is in C1, name3 is in D1, Etc.

now for the dates May 1 2009 - may 31 2009 in A1 - A31, then June 1 2009 to june 30 2009 A32 to A61

the problem that i have is is that im trying to apply the VBA code to different ranges, i have the first but which works well, but i dont know how to apply it to the ranges in column C, Column D, etc. please see code below

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count = 1 And Not (Application.Intersect(Target, Range("B3:B5")) Is Nothing) Then
If 1 < Application.CountIf(Range("B3:B5"), Target.Value) Then
Application.EnableEvents = False
Target.ClearContents
MsgBox "You Are not Allowed to Schedule the Same Person Twice in the same Month"
Application.EnableEvents = True
End If
End If
End Sub

This code prevent Duplicate within a particular range

Please Help
Regards
Nick

 

[mikerickson]

Perhaps something like

Private Sub Worksheet_Change(ByVal Target As Range)
  With Target.EntireColumn
    If .Cells.Count = 1 And Not (Application.Intersect(Target, .Range("A3:A5")) Is Nothing) Then
   
        If 1 < Application.CountIf(.Range("A3:A5"), .Value) Then
            Application.EnableEvents = False
            .ClearContents
            MsgBox "You Are not Allowed to Schedule the Same Person Twice in the same Month"
            Application.EnableEvents = True
        End If
    End If
  End With
End Sub

If you want to restrict it to only some columns the first IF can be modified to include a test of .Column

 

[Nico Learning]

Thank you very much for your speady reply,

How can i Apply this to Range (J6:26) and also (F27:F48) but do not affect the rest of the cells

I am really grateful for your help


Regards
Nick