VB Scripting help

jharih1

New Member
Joined
Dec 5, 2017
Messages
14
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.
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

jharih1

New Member
Joined
Dec 5, 2017
Messages
14
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...

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,822
Messages
5,598,304
Members
414,224
Latest member
Crazy_FC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top