Help creating a working inventory tracking system

How's it going all?

I am trying to replicate a similar excel file my work has for inventory management. Someone who no longer works for my company created the document and kept it protected with passwords and I cannot modify it by any means. It doesn't quite fit what we need it to do anymore so I wanted to create my own and adjust some things.

My production machine produces stock that we put onto 20 different Work In Process lines via an automatic transfer car (T-Car). A T-car on the other side of the WIP lines move stock to the machine it needs to go to. The main screen of the Excel sheet should show all 20 WIP lines and the stock that is currently on them. For the T-Car that is putting the stock on the lines, I need an "Add Stock" button that will insert a stock # in the column closest to the other T-Car. There should also be a "Remove stock" button that will pull off stock the same way from the last entered in stock #. The other T-Car operator will need to have a similar sheet that has the stock #'s on them, but when they "Remove stock" it will remove it from the right hand side of the sheet.

There were originally 2 files labeled Corrugator WIP inventory and Converting WIP inventory. They both had different options but the stock numbers entered into 1 would also populate on the other sheet.

I attached an Excel file of what I am trying to create. Corrugator WIP Program.xlsm. I just dont know enough about the programming of macros to get "Add Stacks" to give me a pop up that says "What is the stock #", after that box, it should say "What WIP line is the stock going on?" after that gets entered, the number goes to that correct row and all the way closest to the Converting Tcar spot.

I am hoping this is clear enough to someone and doesn't sound like gibberish. Any and all help is super appreciated!

Thanks!

I thought I would have some fun with this one.

The application stores the Material Numbers in a database and reads and fills in numbers in the appropriate cells.

As an initialization step, clear out the cells on the WIP sheet, and fill it in with the current data. Put the stacks in the cells appropriate to the line and what column they should be in. Then click the Refresh Stock Button. This action erases everything in the database and repopulates the database with the stacks in the correct places.

To add a stack, use the dropdown list on Cell N2 to select the line. In Cell N3 enter in the desired value manually. The row number and column number are computed by the program. Do not change these fields. Click on the add button. The database will be refreshed and the "matrix" on the WIP sheet will be redreshed.

To delete a stack, Use the dropdown in cell N15. The rest of the data in that column are computed by the program. Do not change these fields. Click on the Delete button. The record is deleted from the database and if there are any items to the left of the deleted items they are moved over to the right.




Columns M:N contain data that is used by the program.