Excel Inventory Management

kwvh
3StarLounger
Posts: 308
Joined: 24 Feb 2010, 13:41

Excel Inventory Management

Post by kwvh »

I have a spreadsheet with Qty, In and Out. I want to add a button/control that will allow user to enter amounts in In and Out and not only update the quantity on hand, (easy with a formula), but I want it to update the Quantity to the new value then set the In and Out cells to 0.

Any suggestions?

Thank you in advance.

kwvh

User avatar
HansV
Administrator
Posts: 68737
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Excel Inventory Management

Post by HansV »

Long time no see!

You can add a command button from the Form Controls section, and assign the following macro to it:

Code: Select all

Sub Button_Click()
    Range("Qty").Value = Range("Qty").Value + Range("In").Value - Range("Out").Value
    Range("In").Value = 0
    Range("Out").Value = 0
End Sub
Replace Qty, In and Out with the corresponding cell addresses (or alternatively, you can name the relevant cells Qty, In and Out and use the code as posted).
Regards,
Hans

kwvh
3StarLounger
Posts: 308
Joined: 24 Feb 2010, 13:41

Re: Excel Inventory Management

Post by kwvh »

Thanks Hans!!!

Yes sir, it has been a long time. I haven’t been in the computing world in a while, but good to be back!!