Or clearing the color and using vba FIND instead of a function.
BTW Most agree that using application.worksheetfunction should just be
application.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim mf As Range
If Target.Count > 1 Then Exit Sub
Target.Interior.ColorIndex = 0
Set mf = Cells(2, 2).Resize(Target.Row - 2).Find(What:=Target, _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If Not mf Is Nothing Then
MsgBox "Value used, Use another"
Target.Interior.ColorIndex = 6
End If
End Sub
Don Guillett
SalesAid Software
dguille...@gmail.com
From: Mahesh parab
Sent: Saturday, February 11, 2012 12:38 PM
To: excel-macros@googlegroups.com ; rajey...@gmail.com
Subject: Re: $$Excel-Macros$$ alert for duplicate name or number while entering
the data in the sheet
Hi Rajesh
find attach workbook hope its as per your requirement
for any column in workbook .This will give you alert while entering duplicate
entry with msg box & column header name
highlight the duplicate entry
Right click on sheet tab-> view code->copy below code
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Application.WorksheetFunction.CountIf(Range(Cells(2, Target.Column),
Cells(Target.Row, Target.Column)), Target) > 1 Then
MsgBox "Already Exist " & Cells(1, Target.Column)
Target.Interior.ColorIndex = 6
End If
End Sub
Perhaps ! you can use msgbox as well whether to keep or delete record
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Application.WorksheetFunction.CountIf(Range(Cells(2, Target.Column),
Cells(Target.Row, Target.Column)), Target) > 1 Then
Alert = MsgBox(Cells(1, Target.Column) & " Already Exist - Click Yes do
delete", vbYesNo)
If Alert = vbYes Then
Application.Undo
Else
Target.Interior.ColorIndex = 6
Exit Sub
End If
End If
End Sub
HTH
Mahesh
On Sat, Feb 11, 2012 at 8:51 PM, Bé Trần Văn <betnmtdongna...@gmail.com> wrote:
2012/2/11 raghu gr <balaji.bra...@gmail.com>
hi,
its limiting the entry also.
with regards
raghu
On Sat, Feb 11, 2012 at 5:50 PM, Bé Trần Văn <betnmtdongna...@gmail.com>
wrote:
2012/2/10 rajeyssh aggarwaal <rajey...@gmail.com>
Hello Sir,
Is it possible while we entering the date in the sheet system alert us
that the name or number or text already in the sheet but not limit the entry of
the data, if yes let me know how can we do it and is it possible to imply the
rule in few rows not in all the rows.
thanks & regards
rajesh
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook.
Forum owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
Validation of data entry to avoid duplicate values (Excel 2003).
From the menu, choose Data, select Validation, Making Data Validation box
appears select the Settings tab, in the Allow: select Custom, enter the formula
in the Formula frame (for column B) as follows:
= MATCH (B2, $ B: $ B, 0) = ROW (B2)
Next Error Alert tab, in the frame enter the text Error message "you have
duplicate data entry", then press the OK button
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
When not used for duplication, only apply to those cases only for a time such
as ATM card, ID number, certificate number .. v .. v ..
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
