PDA

View Full Version : Solved: Help: How to set a column Value become Key Index



yurble_vn
12-06-2007, 05:46 AM
Hi All,

Is there anyway to set a column become key index?

For example, column A, whenever I enter a number into the column, if the number is already in the above list, there will be an alert.

Thanks.

figment
12-06-2007, 06:42 AM
give this a try

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Dim a As Long
If Target.Column = 1 Or Target.Column = 2 Then
a = Target.Row
While Target.Offset(a - Target.Row, 0) <> "" And a <> 0
If Target = Target.Offset(a - Target.Row, 0) Then
Target = "ERROR" '<- change this to what ever error message you want
End If ' if there is no error nothing will happen
a = a - 1
Wend
End If
Application.EnableEvents = True
End Sub
right now it will run on column a and b, change add or subtract column numbers in the first if statment to change you key columns

rory
12-06-2007, 07:04 AM
If you are not worried about copying and pasting (which would overwrite the settings), you can use Data Validation to achieve this.

yurble_vn
12-06-2007, 10:10 AM
Hi Rory,

I also think of Validation.

But really dont know how to put it into work.

Please give some hint.

thanks all for prompt feedback

yurble_vn
12-08-2007, 11:42 AM
FOund the solution:

Use validation with custom and this formula:
(with assumption that column A is the primary key)
=countif($A:$A,A1)=1