Inventory worksheet

[tourcat]

Hello,

I'm trying to make a worksheet to track inventory, not for sales but for tracking how many supplies are on hand in storage rooms, closets, etc. The worksheet will be used by as many as 20 different users probably on a network.

Because it will be used by many people, I'm trying to see if there is a way that the worksheet can be updated after every save. In particular, the amount that are "on hand".

For example, say there are 10 of a particular item on hand to start. Joe takes 2 and goes to record it on the worksheet. He inputs the 2 he takes, which now show that there are 8 on hand remaining. He saves the worksheet. Then, Jerry comes and takes 2 and goes to record it on the worksheet.

I can't figure out how, or if it's possible, to have the worksheet show Jerry that there are 8 on hand to start, not 10. So that when he takes his 2, it should show that there are now 6 on hand remaining. I've attached an example worksheet. Thanks in advance for your help!

[mikerickson]

Why is the "Do you want to save changes" message box insufficiant?

[Kuskush]

Hi
this is the code that does what you need. It has to be put in the code-page
for the Workbook (not in a Module!)
[open the VisualBasic Editor by Alt-F11 and select ThisWorkbook in the VBA-Project Explorer]
The Code will be run whenever the Exel-File is saved if Macros where activated when opening it.

Good luck

Please Login or Register  to view this content.

[Leith Ross]

Hello Kuskush,

I added code tags to your post. Here is how to do it next time...

[code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code]

Sincerely,
Leith Ross

[royUK]

Place this code in the Worksheet_SelectionChange event.

Please Login or Register  to view this content.

[tourcat]

Thanks everyone for your code, although I'm having issues when I try to protect the sheet.

I don't necessarily want anyone to just go in and change things such as the item # or description. The only thing they should have the power to change is the column for "Quantity Taken". But when I unlock this column and protect the sheet, it gives errors and attempts to debug the code when I save a change. This is the part that is highlighted and assume the error comes from this section of the code:

Please Login or Register  to view this content.

This is the total code:

Please Login or Register  to view this content.

[Kuskush]

Setting UserInterfaceOnly to true will allow the Macro to access the protected cells :

Please Login or Register  to view this content.