VBA Popup Window by value

[Watchdawg]

Hello folks. Been searching but haven't found anything quite getting me what I need.
I have a spreadsheet with 2 tabs on it. First tab is titled "test list", this contains a short list of part numbers
Second tab is where the customer enters their data in cell A1. This is a part number. If that part number exists on tab 1, range A1:A21, I need it to pop up a message box warning them that they're moving the wrong product.

Is this even possible? I can't find something to point it to a range on a different tab.

Thanks in advance!

 

[gallen]

It's a little vague but this should point you in the right direction.

Many ways to achieve your goal. Here is one way:

Open the code window of the 2nd sheet where the user enters a part number into A1 and past this code, ensuring you change the sheet 2 name accoridngly (you missed that sheet name out of your OP). Ive highlighted it in red below

Sub Check()
    Dim rList As Range
    Dim sPartNo As String
    
    
    Set rList = Sheets("test list").Range("A1:A21")
    
    sPartNo = Sheets("[B][COLOR=#ff0000]Sheet2[/COLOR][/B]").Range("A1") 'Change sheetname accordingly
    
    If rList.Find(sPartNo) Is Nothing Then
        'code to run if part no isn't in list
    Else
        MsgBox "You are moving the wrong product!", vbCritical, "Please Check"
        'Any other code to run if partno exists
    End If
    
    
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then Check
End Sub

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)

   Dim Fnd As Range
   
   If Target.CountLarge > 1 Then Exit Sub
   If Target = Range("A1") Then
      Set Fnd = Sheets("[COLOR=#ff0000]Sheet1[/COLOR]").Columns(1).Find(Target.Value, , , xlWhole, , , False, , False)
      If Not Fnd Is Nothing Then MsgBox "Wrong Product"
   End If
End Sub

Changing sheet name in red to suit