If i enter an Invoice number..etc on my Data Input Sheet it then sends the data to the Data Sheet Via Macro. Is there a Formula i can put into the (Invoice #)Cell ,that when i type in an Invoice it would Alert me that its already used?
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Alert the User when a Duplicate Value is Input
0
Post Edited
Title: Title was not descriptive.
Discussion
Yea, how do you want it to alert you? A pop-up window? A value in another cell? Conditional formatting? There are a lot of things that can be done.
don (rep: 1989) Apr 2, '19 at 4:26 am
A pop up window please. Thanks in advance.
Learn (rep: 2) Apr 2, '19 at 9:30 am
What is the best way of doing it?or what are your suggestions? A value in another cell would also be good. Say the cell beside or on the same worksheet.
Learn (rep: 2) Apr 3, '19 at 9:24 am
Pop-up window is fine. You just need to do a simple search with a macro that will see if the value is already input into somewhere else. I'll get you a simple example in an Answer.
don (rep: 1989) Apr 5, '19 at 3:29 pm
Answers
0
Here is a macro that you can use:
Sub find_value(Target)
Dim SHT As Worksheet
Dim rFND As Range
strSearch = Target.Value
If strSearch <> "" Then
Set rFND = Nothing
' Sheet2 is the name of the sheet where the value might already be contained.
Set rFND = Sheets("Sheet2").Cells.Find(What:=strSearch, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlRows, SearchDirection:=xlNext, MatchCase:=False)
If Not rFND Is Nothing Then
msg_response = MsgBox("Value already exists.", vbOK)
Target.Clear
GoTo OutsideLoop
End If
End If
OutsideLoop:
End SubIt is modified from the code here: Macro to Find All text or values in the workbook
Then, in the worksheet where you want the macro to run, input this macro code:
Private Sub Worksheet_Change(ByVal Target As Range)
' You can also use Target.Column = 1 or 2 or 3 etc.
' 1 = column A, 2 = Column B, etc.
If Target.Address = "$A$1" Then
Call find_value(Target)
End If
End SubThis is what will run each time the user changes a cell value in the worksheet; it will only run in the workshet that you put it into.
To get to the code window for a worksheet, right-click its tab and click View Code.
You can learn more about the second macro here: Run a Macro When a Specific Cell Changes in Excel