VB Scripting help

[jharih1]

I wish to compare an entry that a user enters in a field to a table housed in Access. If this entry is not in that list, I want to give an error saying that the entry is invalid.

We have some manual data being requested by our team to calculate savings, however certain entries are invalid and they use a generic 999999 number for this vendor field, which causes savings calculations not being captured, or errors in the end product. So we need a validation point somehow.

So column in my excel sheet will say "Vendor number", which will have a refrence somehow in the access table also called Vendor number. This data is too huge for me to bring it into the excel spreadsheet for data validation/vlookups.

 

[jharih1]

Guys - after much search through multiple forums I have had zero help on this issue. So I went for a plan B - I imported the lookup table into a separate xslx file (Vendor.xslx). Now, what I need to do is if a particular field (VendorNumber) on the worksheet is being changed and the value is not on the vendor data, then the user should get a MsgBox saying "Please enter valid vendor info." I am sure my code below is rubbish, but any point in the right direction would help. I do not wsh to create a macro - but this should be a background "check" within the sheet, which is why I did the code within the Private sub worksheet...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim sForm As String
    If Intersect(Target, Range("i2:i9999")) Is Nothing Then Exit Sub
    On Error Resume Next
        sForm = Target.Validation.Formula1
    On Error GoTo 0


Dim MyLookup As Range
Set MyLookup = Workbooks("C:\Users\H204418\Documents\Projects\VendorNumber\Vendor.xlsx").Worksheets("Sheet1").Range("")
    If ActiveCell.Value = WorksheetFunction.VLookup(ActiveCell.Offset(0, -3).Value, MyLookup, 2, False) Is Error Then 'Check if entry in cell is in vendor list
    MsgBox "Please enter valid vendor number." 'return error msgbox if vendor entry not in vendor list.
    End If
    
End Sub