Multiple Excel Worksheets Sharing the same VB Code

[MS-Excel-Rookie]

The purpose of the Excel Spreadsheets is to contain the Formulas for creating the products that the company sells. Production Users will enter information such as Compound Names, Lot Numbers, and amounts (weights) of each compound that is to be used. The Spreadsheet has the appropriate formulas to do the proper checking, and VB Code that will verify that the Lot Number entered contains the appropriate compound.

The same VB Code is able to work for all of the Spreadsheets, since they all have the same relative structure (each row is formatted the same way, although not all of the Spreadsheets have the same number of rows). This will result in numerous Spreadsheets that all contain the same VB Code (over 20 so far, and over 1500 expected by the end of the project).

Is there any way for all of the Spreadsheets to share the VB Code? I can only imagine that Maintenance of the VB Code will be come a nightmare over time if each sheet requires its own copy.
​

<!-- / message --><!-- sig -->

 

[VoG]

Consider creating an Add-in http://www.rondebruin.nl/personal.htm

 

[MS-Excel-Rookie]

Consider creating an Add-in http://www.rondebruin.nl/personal.htm

I have been trying to create an Add-In and am running into issues like undefined Global Variables, Functions, and Procedures. Any advice would be appreciated.