Read all three answers to your original query.
In re, Paul's Data Tier:
we are using a very basic excel, just rows and columns and tabs to keep track of our internal stock
.That pefectly fits. It also meets both P45cal's and mine answers.
We all three also discussed the Presentation Tier. To Expand on Paul a bit, this include Reports, things to be viewed by people, things to be Printed, and things to be saved. The Presentation Tier also includes UserForms as a GUI for people to use to create the previously mentioned "Things," and for Data Entry and maintenance, (Editing and deleting as needed)
All thing Programming proceeds from Data, and Reports are a form of data.
Excel Data Bases/Data Tables, should always be unformatted. The only recommended formatting on a Data sheet is to change the Header's Font (Name or Bold) and to bottom Border the headers. The Many-Small-Table format I mentioned in my first post was designed to make it easy for Building contractors to make their own DB, while making it possible for me to code for unknown Data. I doubt that it would be the best style for you, I think a standard Flat DB is what you should use. You might want to include what I then called Categories into your DB as Fields.
A Small language primer:
DB table: XL Sheet (except LookUp Table Sheets With Many Index Tables)
DB LookUp or Index Table: A Primary Key to Primary Key cross reference between Tables
DB Table name: Xl Sheet Tab Name or CodeName (Multi-Column Header on a Many-Table sheet)
DB Field Name: Xl Header
DB Record: XL Row
DB Field: XL one Cell in a Row
DB Primary Key: XL column A (First Table Column on a Many-Table sheet) An Item specific identifier
DB RecordSet: XL Table Row
Data Point: A Single field
Index Tables are handy to cross reference Part IDs to Supplier IDs When you use a separate Table for Suppliers.
The first step I would do is
is that i wrote a whole new database for materials so it would be more easy to link them in the future as they now all have the same uniform ID, and added EN codes. it was just a logical step to upgrade the excel for stock also...
Make sure that table is Flattened and without formatting.
The next step would be to create blank Templates for every Report, thing to be viewed by people, thing to be Printed, and thing to be saved. Compare all Data points in all Templates to the Fields/Headers in the Data Tables/Sheets.
Only after that would I begin to work on the final phase of the Presentation Tier: The UserForms that all Users would use to interact with the Data and the rest of the Presentation Tier.
One place where my own style of programming differs from the usual is that I keep all Middle Tier code that relates to Data Puts and Gets in the Code module of the Data Sheet. I find this more programmatically logical than the usual style of using Standard Modules for this specific purpose. Example, assuming a Control(s) on some of the UserForms need a list of Thicknesses of a specific Material:
Public Property Get Thicknesses(Material As String) as Variant
Thicknesses = CustomFunction(Material)
End Property
Private Function CustomFunction() As Variant
'Well developed Code to return an array of all available thicknesses of an inputted Material from the table on this sheet
CustomFunction = that array
End Function
That Class Property Get will return an array of Thicknesses to whatever code requests it via
X = TableName.Thicknesses(Material)
that code in that Sheet can be directly copied to any other Sheet (table) without editing and can be used by any code by merely changing the table name (Tab name or CodeName) used in the request.