Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prohibit duplicate data in a cell
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prohibit duplicate data in a cell
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 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA to delete Duplicate Records (1 column), before which, non-duplicate data merged into remaining row | Excel Programming | |||
Delete Duplicate data row wise from each cell | Excel Programming | |||
delete duplicate data in the same cell | Excel Programming | |||
Deleting duplicate data - inc original cell | Excel Programming | |||
Duplicate data in a cell... | Excel Worksheet Functions |