If i have a range of cells from A1:A100 and the same drop down list of names
in each of those 100 cells how do i prevent staff from selecting the same
name twice within that range?

For example i want to avoid someone selecting a name in cell A3 and then
selecting the same name further down the spreadsheet at say A70.

Is it also possible to display a message saying that a duplication has been
attempted?

Thanks in advance
Craig

Data validation is used for drop down list therefore you need an event macro
for that:

Private Sub Worksheet_Change(ByVal Target As Range)
If WorksheetFunction.CountIf(Columns(Target.Column), Target) 1 Then
MsgBox "Duplication"
Application.EnableEvents = False
Target = ""
Application.EnableEvents = True
End If
End Sub

Post if you need help to install it!
Regards,
Stefi

€žCraig€� ezt Ã*rta:

If i have a range of cells from A1:A100 and the same drop down list of names
in each of those 100 cells how do i prevent staff from selecting the same
name twice within that range?

For example i want to avoid someone selecting a name in cell A3 and then
selecting the same name further down the spreadsheet at say A70.

Is it also possible to display a message saying that a duplication has been
attempted?

Thanks in advance
Craig

Hi Stefi,

I've never ran an event macro before. How to i go about it?

Thanks

"Craig" wrote:

If i have a range of cells from A1:A100 and the same drop down list of names
in each of those 100 cells how do i prevent staff from selecting the same
name twice within that range?

For example i want to avoid someone selecting a name in cell A3 and then
selecting the same name further down the spreadsheet at say A70.

Is it also possible to display a message saying that a duplication has been
attempted?

Thanks in advance
Craig

Sorry for the late answer but I've just now realized that my e-mail
notification doesn't work, I thought there is no answer.
If it's still of interest, here is the answer:

Open VBA (Alt+F11)!
Right click on your worksheet name in the Project explorer window!
Select View code from the popup menu!
Copy/Paste macro code in the code window!

--
Regards!
Stefi



€žCraig€� ezt Ã*rta:

Hi Stefi,

I've never ran an event macro before. How to i go about it?

Thanks

"Craig" wrote:

If i have a range of cells from A1:A100 and the same drop down list of names
in each of those 100 cells how do i prevent staff from selecting the same
name twice within that range?

For example i want to avoid someone selecting a name in cell A3 and then
selecting the same name further down the spreadsheet at say A70.

Is it also possible to display a message saying that a duplication has been
attempted?

Thanks in advance
Craig

How can I make this work on a specific column? I want duplicates to be
allowed in every column except A

Billy

"Stefi" wrote:

Data validation is used for drop down list therefore you need an event macro
for that:

Private Sub Worksheet_Change(ByVal Target As Range)
If WorksheetFunction.CountIf(Columns(Target.Column), Target) 1 Then
MsgBox "Duplication"
Application.EnableEvents = False
Target = ""
Application.EnableEvents = True
End If
End Sub

Post if you need help to install it!
Regards,
Stefi

€žCraig€� ezt Ã*rta:

If i have a range of cells from A1:A100 and the same drop down list of names
in each of those 100 cells how do i prevent staff from selecting the same
name twice within that range?

For example i want to avoid someone selecting a name in cell A3 and then
selecting the same name further down the spreadsheet at say A70.

Is it also possible to display a message saying that a duplication has been
attempted?

Thanks in advance
Craig

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 1 Then Exit Sub
If WorksheetFunction.CountIf(Columns(Target.Column), Target) 1 Then
MsgBox "Duplication"
Application.EnableEvents = False
Target = ""
Application.EnableEvents = True
End If
End Sub


Gord Dibben MS Excel MVP


On Wed, 9 Dec 2009 04:43:01 -0800, billy
wrote:

How can I make this work on a specific column? I want duplicates to be
allowed in every column except A

Billy

"Stefi" wrote:

Data validation is used for drop down list therefore you need an event macro
for that:

Private Sub Worksheet_Change(ByVal Target As Range)
If WorksheetFunction.CountIf(Columns(Target.Column), Target) 1 Then
MsgBox "Duplication"
Application.EnableEvents = False
Target = ""
Application.EnableEvents = True
End If
End Sub

Post if you need help to install it!
Regards,
Stefi

„Craig” ezt írta:

If i have a range of cells from A1:A100 and the same drop down list of names
in each of those 100 cells how do i prevent staff from selecting the same
name twice within that range?

For example i want to avoid someone selecting a name in cell A3 and then
selecting the same name further down the spreadsheet at say A70.

Is it also possible to display a message saying that a duplication has been
attempted?

Thanks in advance
Craig

Thanks for the help it worked great!!!

"Gord Dibben" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 1 Then Exit Sub
If WorksheetFunction.CountIf(Columns(Target.Column), Target) 1 Then
MsgBox "Duplication"
Application.EnableEvents = False
Target = ""
Application.EnableEvents = True
End If
End Sub


Gord Dibben MS Excel MVP


On Wed, 9 Dec 2009 04:43:01 -0800, billy
wrote:

How can I make this work on a specific column? I want duplicates to be
allowed in every column except A

Billy

"Stefi" wrote:

Data validation is used for drop down list therefore you need an event macro
for that:

Private Sub Worksheet_Change(ByVal Target As Range)
If WorksheetFunction.CountIf(Columns(Target.Column), Target) 1 Then
MsgBox "Duplication"
Application.EnableEvents = False
Target = ""
Application.EnableEvents = True
End If
End Sub

Post if you need help to install it!
Regards,
Stefi

€žCraig€� ezt Ã*rta:

If i have a range of cells from A1:A100 and the same drop down list of names
in each of those 100 cells how do i prevent staff from selecting the same
name twice within that range?

For example i want to avoid someone selecting a name in cell A3 and then
selecting the same name further down the spreadsheet at say A70.

Is it also possible to display a message saying that a duplication has been
attempted?

Thanks in advance
Craig

.