Inventory worksheet

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!

Hello Kuskush,

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

[code]
'First line of your code
.
.
'Last line of your code
[/code]

Sincerely,
Leith Ross

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:

.Range("D3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

This is the total code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

With ThisWorkbook.Worksheets("Inventory List")
    .Range(.Cells(3, 6), .Cells(Rows.Count, 6).End(xlUp)).Copy
    .Range("D3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    .Range(.Cells(3, 5), .Cells(Rows.Count, 5)).ClearContents
    .Range("E3").Select
End With
 
End Sub