Prev: macro to update last character
Next: WaitForString Mutiple Conditions
From: Rich on 23 Feb 2010 16:37
I need to prohibit the use of duplicate part numbers in a certain range of
cells.

Using the worksheet_change event, I create an array of all existing part
numbers. I can then go thru that array and compare it with the value of the
cell that the user just typed a part number into.

If I find that the part number that the user just typed in is a duplicate, I
don't want to allow the focus to shift to another cell, and I want the cell
value to be highlighted....similar to "data validation" where the data won't
be accepted into the cell if it is invalid. I'm not sure how to do this part
of the task.

Can anyone point me in the right direction?? TIA.
From: Chip Pearson on 23 Feb 2010 16:57



You can use Data Validation to do this. Suppose you range to test is
A1:A10. Select that range, open the Data Validation dialog, choose
"Custom" from the Allow list, and use the following formula as the
valdiation formula. If the formula returns TRUE, input is allowed. If
the formula returns FALSE, input is prohibited.

=COUNTIF($A$1:$A$10,A2)=1

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com


On Tue, 23 Feb 2010 13:37:01 -0800, Rich
<Rich(a)discussions.microsoft.com> wrote:

>I need to prohibit the use of duplicate part numbers in a certain range of
>cells.
>
>Using the worksheet_change event, I create an array of all existing part
>numbers. I can then go thru that array and compare it with the value of the
>cell that the user just typed a part number into.
>
>If I find that the part number that the user just typed in is a duplicate, I
>don't want to allow the focus to shift to another cell, and I want the cell
>value to be highlighted....similar to "data validation" where the data won't
>be accepted into the cell if it is invalid. I'm not sure how to do this part
>of the task.
>
>Can anyone point me in the right direction?? TIA.
 | 
Pages: 1
Prev: macro to update last character
Next: WaitForString Mutiple Conditions