Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Warning of duplicates
If a user enters a number in column 'D' which has already been used, rather
than simply identifying that the number has been used already, how can I get a message box to appear asking if they want to continue using that particular number or not? If they select yes, end sub, if they select no, target.value = vbnullstring. It is purely a warning. In this case, dupes are allowed but I want to alert the user that the number they have entered has already been used therefore eliminating accidental dupes (hopefully). Can code inform the user that dupe(s) are on row xx? Thanks, -- Traa Dy Liooar Jock |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Warning of duplicates
Jock, try this worksheet code
Private Sub Worksheet_Change(ByVal Target As Range) Dim MyRow As Long If Target.Column < 4 Then Exit Sub If WorksheetFunction.CountIf(Range("D:D"), Target.Value) 1 Then MyRow = WorksheetFunction.Match(Target.Value, Range("D:D"), 0) response = MsgBox("That number has been used on row " _ & MyRow & " Yes to continue NO to cancel", vbYesNo, "Warning") If response = vbYes Then Exit Sub Else Target.ClearContents End If End If End Sub Mike "Jock" wrote: If a user enters a number in column 'D' which has already been used, rather than simply identifying that the number has been used already, how can I get a message box to appear asking if they want to continue using that particular number or not? If they select yes, end sub, if they select no, target.value = vbnullstring. It is purely a warning. In this case, dupes are allowed but I want to alert the user that the number they have entered has already been used therefore eliminating accidental dupes (hopefully). Can code inform the user that dupe(s) are on row xx? Thanks, -- Traa Dy Liooar Jock |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Warning of duplicates
U can use data validation
Select the Range A1:A10 and now Go to Data | Data Validation | setting : Allow - custom | Formula : =COUNTIF(A$1:A$10,A1)=1 | ok On Oct 19, 2:32*pm, Jock wrote: If a user enters a number in column 'D' which has already been used, rather than simply identifying that the number has been used already, how can I get a message box to appear asking if they want to continue using that particular number or not? If they select yes, end sub, if they select no, target.value = vbnullstring. It is purely a warning. In this case, dupes are allowed but I want to alert the user that the number they have entered has already been used therefore eliminating accidental dupes (hopefully). Can code inform the user that dupe(s) are on row xx? Thanks, -- Traa Dy Liooar Jock |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Warning of duplicates
Hi Jock
This event code has to be pasted into the code sheet for your worksheet. Private Sub Worksheet_Change(ByVal Target As Range) Dim Dupes() Dim SearchAfter As Range Set isect = Intersect(Target, Columns("D")) If Not isect Is Nothing Then Count = WorksheetFunction.CountIf(Columns("D"), Target.Value) If Count 1 Then msg = MsgBox("The number just entered has been used before" _ & vbLf & vlf & "Do you vant to keep this value? ", _ vbYesNo + vbQuestion, "Duplicate") If msg = vbNo Then Target.Value = "" Else ReDim Dupes(1 To Count - 1) Set SearchAfter = Target For r = 1 To Count - 1 Set f = Columns("D").Find(what:=Target.Value, _ after:=SearchAfter, Lookat:=xlWhole, LookIn:=xlValues) Dupes(r) = f.Row Set SearchAfter = f Next RowString = Dupes(1) If Count 2 Then For r = 2 To Count - 1 RowString = RowString & ", " & Dupes(r) Next End If msg = MsgBox("Dublets are found in row(s): " & _ RowString, vbInformation + vbOKOnly, "Infomation") End If End If End If End Sub Regards, Per "Jock" skrev i meddelelsen ... If a user enters a number in column 'D' which has already been used, rather than simply identifying that the number has been used already, how can I get a message box to appear asking if they want to continue using that particular number or not? If they select yes, end sub, if they select no, target.value = vbnullstring. It is purely a warning. In this case, dupes are allowed but I want to alert the user that the number they have entered has already been used therefore eliminating accidental dupes (hopefully). Can code inform the user that dupe(s) are on row xx? Thanks, -- Traa Dy Liooar Jock |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Warning of duplicates
Hi Mike,
Can this be changed to sit in a userform textbox2 exit event which fires when user exits textbox2? The number is now entered in a userform and transferred to column 'D' (hopefully after your validation code checks) when command (close) button is clicked. Thanks again -- tia "Mike H" wrote: Jock, try this worksheet code Private Sub Worksheet_Change(ByVal Target As Range) Dim MyRow As Long If Target.Column < 4 Then Exit Sub If WorksheetFunction.CountIf(Range("D:D"), Target.Value) 1 Then MyRow = WorksheetFunction.Match(Target.Value, Range("D:D"), 0) response = MsgBox("That number has been used on row " _ & MyRow & " Yes to continue NO to cancel", vbYesNo, "Warning") If response = vbYes Then Exit Sub Else Target.ClearContents End If End If End Sub Mike "Jock" wrote: If a user enters a number in column 'D' which has already been used, rather than simply identifying that the number has been used already, how can I get a message box to appear asking if they want to continue using that particular number or not? If they select yes, end sub, if they select no, target.value = vbnullstring. It is purely a warning. In this case, dupes are allowed but I want to alert the user that the number they have entered has already been used therefore eliminating accidental dupes (hopefully). Can code inform the user that dupe(s) are on row xx? Thanks, -- Traa Dy Liooar Jock |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find duplicates, sum column then delete duplicates | Excel Programming | |||
Check for Duplicates then Sum cells of duplicates | Excel Programming | |||
Condensing a list with duplicates to a list with non-duplicates | Excel Worksheet Functions | |||
Duplicates in excel that aren't 100% DUPLICATES ... | Excel Discussion (Misc queries) | |||
Warning Box | Excel Programming |