VBA script

[jharih1]

We have an issue where end users have to update a spreadsheet with data, which then feeds another application. However, because end users are not following the format, or entering bogus numbers in a field, our data gets corrupted on the end. I would like to create a VB script that checks whether a particular number listed is valid. The validity is a lookup function to available numbers that are housed in a Access Database table. If its not valid, I want to have a message pop up, or notification to the person entering the data that their number listed is not valid.

I am not a coder, but I know only VB script can do what I am asking it to. I also cannot house the data from the database table within excel because there are too many records.

Thanks in advance!

 

[Kenneth Hobson]

Welcome to the forum!

There should be several examples using ADO. Other methods might be a QueryTable.

 

[jharih1]

Thank you! I tried searching but I couldn't find any helpful examples. Perhaps this is due to my ignorance on scripting too.

 

[Kenneth Hobson]

There are many examples. Search for "Excel ADO LookUp". e.g. https://msdn.microsoft.com/en-us/library/ms808325.aspx