Restricting data entry to unique entries only in a specific range

[Guest]

I can restrict data entry to unique entries only in the range E7 to E500
using data validation:
=IF(COUNTIF($E$7:$E$500,E7)=1
However unless I lock down sheet to stop copying and pasting this quickly
becomes worthless. How do I accomplish the same thing in VBA? I need bullet
proof validation while leaving the sheet flexible enough to allow cutting and
pasting.

 

[Dave Peterson]

I don't think you can bullet proof Data|Validation. I've always considered it a
training issue--making sure that they don't edit|copy followed by edit|Paste
over a cell with data|validation which destroys the existing data|validation
rules.

You may be able to use some sort of event macro, but you can't make this bullet
proof, either. If the user disables macros or disables events, then that breaks
the event handler.

I think I'd live with Data|Validation (and some training) along with a cell with
a formula in it:

Maybe something like this in E6 (Formatted in big bold red letters):
=IF(COUNTA($E$7:$E$500)
=SUMPRODUCT(($E$7:$E$500<>"")/COUNTIF($E$7:$E$500,$E$7:$E$500&"")),
"","Duplicates!")

(all one cell)

Or even put your formula in F7
=if(countif($e$7:$e$500,e7)=1,"","Duplicate")
(and drag to F500)

 

[Guest]

Thank you for your reply. Unfortunatly this is very similar to the solution
that currently exists I have been tasked with locking it down further. At the
moment if a duplicate is detected (very similar formula to the ones you
mentioned) the user is unable to perform further actions (i.e. press an
export button that FTP's the data). This is seen to be confusing even with a
big "You have a duplicate" message, so what I need is a VBA solution that
prevents them from entering a duplicate at the point of manual data entry /
copy paste etc. Hope you or someone else can help.

 

[Dave Peterson]

If macros are disabled or events are disabled, then this won't work. (Formulas
will work with macros disabled.)

If you want to try, rightclick on the worksheet tab that should have this
behavior. Select view code and paste this into the code window:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim NumberOfUnique As Long
Dim HowManyFilledIn As Long
Dim myRng As Range

With Me
Set myRng = .Range("e7:e500")
If Intersect(Target, myRng) Is Nothing Then Exit Sub
NumberOfUnique = .Evaluate("SUMPRODUCT((" & myRng.Address _
& "<>"""")/COUNTIF(" & myRng.Address & "," _
& myRng.Address & "&""""))")
HowManyFilledIn = Application.CountA(myRng)
End With

If NumberOfUnique = HowManyFilledIn Then
'all unique, do nothing
Else
With Application
.EnableEvents = False
.Undo 'go back
.EnableEvents = True
End With
MsgBox "You had a duplicate. The last change was undone!"
End If
End Sub

 

[Guest]

Thanks again Dave. I got a runtime error '13': type mismatch when using the
code when it hit this bit:

NumberOfUnique = .Evaluate("SUMPRODUCT((" & myRng.Address _
& "<>"""")/COUNTIF(" & myRng.Address & "," _
& myRng.Address & "&""""))")

any ideas?

 

[Dave Peterson]

Do you have any errors in E7:E500?

Thanks again Dave. I got a runtime error '13': type mismatch when using the
code when it hit this bit:

NumberOfUnique = .Evaluate("SUMPRODUCT((" & myRng.Address _
& "<>"""")/COUNTIF(" & myRng.Address & "," _
& myRng.Address & "&""""))")

any ideas?

 

[Guest]

I have nothing in E7:E500 to start with when I enter the number 1 in cell E7
and press return I get the run time error. I'm testing this on a clean
spreadsheet with nothing else in it so I don't think it's that... Thanks for
your efforts so far much appreciated.

 

[Dave Peterson]

I couldn't duplicate the problem on an existing worksheet or a brand new
worksheet.

Can you get this formula to evaluate in a worksheet?

=SUMPRODUCT(($E$7:$E$500<>"")/COUNTIF($E$7:$E$500,$E$7:$E$500&""))

Maybe it's a difference in language--I use English. What do you use?

I have nothing in E7:E500 to start with when I enter the number 1 in cell E7
and press return I get the run time error. I'm testing this on a clean
spreadsheet with nothing else in it so I don't think it's that... Thanks for
your efforts so far much appreciated.

 

[Dave Peterson]

Ps. Try this against a brand new worksheet.

Hit alt-f11 to get to the VBE
hit ctrl-g to see the immediate window
type this and hit enter:
range("a1").Formula _
= "=SUMPRODUCT(($E$7:$E$500<>"""")/COUNTIF($E$7:$E$500,$E$7:$E$500&""""))"

Then look at the formula in A1 to see how it was translated.

Then modify the code to use the same function names.

I have nothing in E7:E500 to start with when I enter the number 1 in cell E7
and press return I get the run time error. I'm testing this on a clean
spreadsheet with nothing else in it so I don't think it's that... Thanks for
your efforts so far much appreciated.

 

[Guest]

Thanks for getting back to me. That all works fine I am using english as well
excel 2002 SP3. The formula resolves to the same syntax and works fine. I can
reproduce the issue every time if I manually input the numbers 1 to 4 in
cells E7:10 and copy those four cells to E11:E14. Otherwise it just seems to
appear randomly as data is entered not always at the same point... the only
thing I can think is maybe it is losing the target of the change. Any ideas?

 

[Dave Peterson]

I tried your test and it worked fine for me.

I'm out of suggestions.

Sorry.

And you're positive that there are no formulas that evaluate to errors in that
range???

Thanks for getting back to me. That all works fine I am using english as well
excel 2002 SP3. The formula resolves to the same syntax and works fine. I can
reproduce the issue every time if I manually input the numbers 1 to 4 in
cells E7:10 and copy those four cells to E11:E14. Otherwise it just seems to
appear randomly as data is entered not always at the same point... the only
thing I can think is maybe it is losing the target of the change. Any ideas?