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.

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)





Illya Teideman wrote:

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

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" wrote:

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)





Illya Teideman wrote:

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

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


Illya Teideman wrote:

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" wrote:

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)





Illya Teideman wrote:

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


--

Dave Peterson

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" wrote:

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


Illya Teideman wrote:

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" wrote:

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)





Illya Teideman wrote:

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


--

Dave Peterson

Do you have any errors in E7:E500?



Illya Teideman wrote:

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" wrote:

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


Illya Teideman wrote:

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" wrote:

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)





Illya Teideman wrote:

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


--

Dave Peterson


--

Dave Peterson