MrExcel Publishing
Your One Stop for Excel Tips & Solutions

no duplicate numbers, date validation

Posted by Paul on December 19, 2001 2:31 PM

can you use data validation to keep people from putting duplicate numbers in a column, say column A1:A50

Posted by Aladin Akyurek on December 19, 2001 2:39 PM

Paul --

Activate A2 (preferably empty).
Activate Data|Validation.
Choose Custom for Allow.
Enter as formula:


Copy A2 down as far as you wish.


Posted by Paul on December 19, 2001 2:43 PM

Thanks, super fast response!

Posted by IML on December 19, 2001 3:49 PM

One validation caveat...

Just as a general rule, be aware that validation does not prevent users from pasting (as opposed to typing)in duplicate numbers etc. If this is a problem for you, I believe some folks could offer you a good vba solution that would prevent this. Foregive me if I'm stating what you already know...

Posted by Qroozn on December 19, 2001 4:07 PM

the One validation caveat...

i' ve found this problem. is there any vba code to disable copy/paste on a worksheet or workbook?

Posted by Jack in UK on December 19, 2001 4:14 PM

Re: the One validation caveat...

Of cause disable grey out the option or remove the toobar/dropdown list when that books opened, re set to default on close


Posted by Juan Pablo G. on December 19, 2001 5:21 PM

Re: the One validation caveat...

You also need to disable Ctrl C, Ctrl V and Ctrl X combinations.. if i remember correctly this is done with this:

Sub DisableCopy()
Application.OnKey "^c", ""
End Sub

Sub EnableCopy()
Application.OnKey "^c"
End Sub

Juan Pablo G.

Posted by Mark O'Brien on December 20, 2001 3:00 PM

Juan - Off Topic

I feel like an idiot. I've used the OnKey command before, but I've never thought to use it to disable keys. As part of my introduction to VBA I was asked if I could block the DELETE key. I said it couldn't be done. (That was two jobs ago).