Check if Value Exists in Another Sheet

[jazz2409]

Hello, how do I change the below code so when I enter a value on column M via paste it will return a messagebox saying "invalid data"?
the below code works however it isn't limited to just column M. it happens to all cells


Private Sub Worksheet_Change(ByVal Target As Range)

Dim FindString As String
Dim Rng As Range
On Error Resume Next


FindString = Range("M2").Value


If Trim(FindString) <> "" Then
With Sheets("IDs").Range("A:A") 'searches all of column A
Set Rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then

Else
MsgBox "Invalid Data!" 'value not found
Target.Clear

End If
End With
End If

End Sub




Please help thank you

[Paul_Hossler]

Not tested





Private Sub Worksheet_Change(ByVal Target As Range)
Dim rFind as Range
Dim FindString As String
Dim Rng As Range
On Error Resume Next

Set rFind = Target.Cells(1,1)

If rFind.Column <> 13 Then Exit Sub

FindString = rFind.Value


If Trim(FindString) <> "" Then
With Sheets("IDs").Range("A:A") 'searches all of column A
Set Rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then

Else
MsgBox "Invalid Data!" 'value not found
rFind.Clear

End If
End With
End If
End Sub

[royUK]

Try this way. Use ClearContents, Clear will remove everything, including formatting. I don't think you need the variable either.

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Or Target.Column <> 13 Or IsEmpty(Target) Then Exit Sub
On Error Resume Next
If Application.WorksheetFunction.CountIf(Sheets("IDs").Range("A:A"), Trim(Target.Value)) > 0 Then
MsgBox "Invalid Data!" 'value exists
Target.ClearContents
Exit Sub
End If
On Error GoTo 0
End Sub

[jazz2409]

Both worked great! :)
Just to share, this is what I came up with on my own:


Private Sub Workbook_Open()

Dim rng As Range


lastrow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row 'gets number of last used cell
Set rng = Range("M2:M" & lastrow)


With rng.Validation
.Delete
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:= _
"=IF(ISERROR(VLOOKUP(M2,IDs!$A$2:$A$65536,1,FALSE)),FALSE,TRUE)"
.ErrorMessage = "INVALID DATA!"
End With
End Sub





May not be the best out there but works just the same :)
I used workbook_open() because my friend's file is in xls

[royUK]

You've moved the code to a different event.

If you are going to use Data Validation then why use VBA?

[jazz2409]

You've moved the code to a different event.

If you are going to use Data Validation then why use VBA?

The file is in xls and won't accept data validation.. Also my friend's client only wants data validation :D

[royUK]

xls files can have data validation, it wouldn't work with VBA if that was not the case.