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
Excel Inventory Management
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Excel Inventory Management
Long time no see!
You can add a command button from the Form Controls section, and assign the following macro to it:
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).
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
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 308
- Joined: 24 Feb 2010, 13:41
Re: Excel Inventory Management
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!!
Yes sir, it has been a long time. I haven’t been in the computing world in a while, but good to be back!!