Automated Excel Material Tracking Workbook

I want to hand over a single Excel file—powered entirely by VBA—that lets my warehouse team record every kilogram of material that comes in or goes out without touching a raw worksheet. A simple, guided userform with dropdown selections (party, PO, batch, process stage, quantity, date) will write each transaction into a properly structured table behind the scenes, so there is no chance of breaking formulas or layouts.

Each night the code should refresh a Daily Summary sheet that shows the total material in and total material out for the day. Running balances, validation checks, and any intermediate calculations must all update automatically when the workbook opens or when a new record is saved. Although today I only need the daily view, I’d like the architecture to be scalable so weekly or monthly roll-ups could be switched on later without redesigning everything.

Key expectations
• Zero manual copy-paste: every path—from data entry to report refresh—needs to be triggered by buttons or workbook events.
• Normalized database table: one row per movement with enforced data types for long-term stability.
• Clear, commented VBA so another analyst can extend it.
• Smooth performance even after several years of records.

I will consider the job complete when:
1. The userform reliably writes, edits, and deletes transactions.
2. The Daily Summary populates correct totals after each new entry and after reopening the file.
3. All calculations remain intact if the file is moved to another PC with standard Office installation.

If this sounds straightforward to you and you have a track record building rock-solid Excel/VBA tools, let’s talk and get a quick prototype in place.