This tool will be very handy for managing your inventory. If you are searching for an inventory management tool or if you are paying several dollars for an inventory management tool, stop it and read this article to build your own inventory management system in Excel.
If you want to start to use the software as soon as possible, then you can:
Otherwise, if you want to DIY, you can read the contents below.
Let’s Prepare the GUI
We need 3 sheets to prepare our own inventory management tool using Excel. Rename Sheet 1 as “GUI”, Sheet 2 as “Database” and Sheet 3 as “List”. On the sheet “GUI”, prepare fields to display Product names as drop down and another field to allow the user to enter the quantity as shown in the image. The drop down will help the user to easily pick a Product rather than typing the name.
Let’s prepare the database
On the sheet “Database”, add headers “Date”, “Product Name”, “Quantity” and “Type” as shown in the image. On the Sheet “List” add these headers “SKU”, “Product Name”, “Description” and “Per Item Price”.
Let’s make it functional
Copy the macro to a new module and attach the macro “StockIN” to the button “StockIn” and the macro “StockOut” to the button “Stock Out”.
Sub p_prod_dropdown() Dim lr As Long Dim r As Long lr = lst.Range("B" & Rows.Count).End(xlUp).Row Dim prodlist As String For r = 2 To lr If prodlist = "" Then prodlist = lst.Range("B" & r).Value Else prodlist = prodlist & "," & lst.Range("B" & r).Value End If Next r With gui.Range("C6:H6").Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=prodlist .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With End Sub Sub StockIN() Dim lr As Long lr = db.Range("A" & Rows.Count).End(xlUp).Row + 1 db.Range("A" & lr).Value = Now() db.Range("B" & lr).Value = gui.Range("C6").Text db.Range("C" & lr).Value = gui.Range("C9").Text db.Range("D" & lr).Value = "IN" End Sub Sub StockOUT() Dim lr As Long lr = db.Range("A" & Rows.Count).End(xlUp).Row + 1 db.Range("A" & lr).Value = Now() db.Range("B" & lr).Value = gui.Range("C6").Text db.Range("C" & lr).Value = gui.Range("C9").Text db.Range("D" & lr).Value = "OUT" End Sub
How does it work?
The Sub “p_prod_dropdown” reads the last used row from the sheet “List”. It then prepares the list of Product names as drop down and saves it on the sheet “GUI”. Now user can use this drop down and select a product. They can enter a quantity and chose “Stock In” or “Stock Out”. The other two macros check for the last available row in the sheet “Database” and save the entry from the sheet “GUI”. When dealing with a huge database it is common to see Excel crashing and getting corrupted. A clean and small database will help you and avoid having a corrupted Excel file.
Graphs shown in the image are not created by the script. However, you can modify the script to create dashboard reports using the data from the sheet “Database”.
Nick Vipond is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including fix word and outlook recovery software products. For more information visit www.datanumen.com.
Leave a Reply