Re: $$Excel-Macros$$ alert for duplicate name or number while entering the data in the sheet
Dear Rajey, Check Below Link.. http://msdn.microsoft.com/en-us/library/gg508922.aspx On Fri, Feb 10, 2012 at 12:30 PM, rajeyssh aggarwaal rajey...@gmail.comwrote: 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 -- Thanks regards, Noorain Ansari ** http://excelmacroworld.blogspot.com/*http://noorainansari.com/* *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.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
Re: $$Excel-Macros$$ alert for duplicate name or number while entering the data in the sheet
** 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
Re: $$Excel-Macros$$ alert for duplicate name or number while entering the data in the sheet
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.comwrote: ** 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
Re: $$Excel-Macros$$ alert for duplicate name or number while entering the data in the sheet
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.comwrote: ** 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
Re: $$Excel-Macros$$ alert for duplicate name or number while entering the data in the sheet
Hi, Try following steps in excel Range a2 1 go to data then data validation 2 settings 3 allow custom 4 formula =countif($a$1:a1,a2)=o 5 copy this cell and paste down with validation This will not allow you to reenter name if you use formula =countif($a$1:a1,a2)0 in conditional formatting then it will highlight when ever their is duplicate entry Thanks and Regards, Somnath C Pardeshi Sent from BlackBerry® on Airtel -Original Message- From: rajeyssh aggarwaal rajey...@gmail.com Sender: excel-macros@googlegroups.com Date: Fri, 10 Feb 2012 12:30:16 To: excel-macros@googlegroups.com Reply-To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ alert for duplicate name or number while entering the data in the sheet 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 -- 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
Re: $$Excel-Macros$$ alert for duplicate name or number while entering the data in the sheet
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.
RE: $$Excel-Macros$$ alert for duplicate name or number while entering the data in the sheet
Hi Don, re. Application.WorksheetFunction / Application … I've noticed people just using Application, but I don't understand why that would be better. I'm intersested to hear your thoughts on that if you prefer that syntax. Since the worksheet functions are only documented as members of Application.WorksheetFunction, I would think that would be the prefereable way to reference them. They have hidden aliases under Application, I would think in order let coders get away with not qualifying them at all (Application being a default library/class). You could use: Application.WorksheetFunction.CountIf(..) WorksheetFunction.CountIf(..) Application.CountIf(..) CountIf(..) Although Application.CountIf will always be specific enough, that's true of all the syntaxes except the completely unqualified one, and I prefer my code to be self-documenting as to referencing a worksheet function. Asa From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of dguillett1 Sent: Saturday, February 11, 2012 12:00 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ alert for duplicate name or number while entering the data in the sheet 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 mailto:mahes...@gmail.com 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
$$Excel-Macros$$ alert for duplicate name or number while entering the data in the sheet
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
