INVENTORY TRACKER I BULID FOR A REATAIL CLIENT:

Recently I have made solution for sell inventory project which is Basically about Shoes inventory management system So after reading the full Blog you will be able to create and track you own Inventory List in Excel.

FAQS:

How to do inventory in Excel?

What Excel formulas is used for inventory?

What VBA CODING is used for inventory?

FOR EASY UNDERSTANDING DIVIDE IT TO PARTS:

Below We are dividing sell Inventory project in parts:

  • Create Sheet Where is we are adding Inventory Data
  • Create a Form for Data Entry
  • Coding for Data Entry Forms Using in Sheets
  • Use formulas in Excel Sheets
  • Format and that’s it.

SO, Let Start First we Need Inventory Data

  1. we have Created “sheet1” and rename it to InventoryData.
  2. Then PRESS ALT+F11 to open VBA Editor.
  3. Then right Click on Left Side Project Explorer and Insert New UserForm.
  4. Rename it to “sellinventory”
  5. Drag and Drop the Required Textboxes and label And Button to UserForm

6. Same To Same Create two Other Form Sell and Purchase.
SELL:

Purchase:

PURCHASE:
In the purchase Form Code for Add Button are given below:
Code:

Private Sub cmdAddUpdate_Click()

    Dim wsData As Worksheet

    Dim lastRow As Long

    Dim productionType As String

    Dim modelCode As String

    Dim colorCode As String

    Dim warehouseName As String

    Dim i As Long

    Dim sizeTextBoxes As Variant

    Dim sizeLabels As Variant

    Dim quantityValue As Variant

    Dim transactionsAdded As Boolean

    Set wsData = ThisWorkbook.Sheets(“InventoryData”)

    ‘ Get input values

    productionType = Trim(Me.txtprod.Text)

    modelCode = Trim(Me.txtModel.Text)

    colorCode = Trim(Me.txtColor.Text)

    warehouseName = Trim(Me.ComboBox1.Text) ‘ Use ComboBox1 for Warehouse

    ‘ Validation

    If productionType = “” Or modelCode = “” Or colorCode = “” Or warehouseName = “” Then

        MsgBox “Please fill in all required fields: Production, Code, Color, and Warehouse.”, vbExclamation

        Exit Sub

    End If

    ‘ TextBox names and size labels

    sizeTextBoxes = Array( _

        “txtSize44”, “TextBox1”, “TextBox2”, “TextBox3”, “TextBox4”, “TextBox5”, “TextBox6”, “TextBox7”, “TextBox8”, _

        “txtS”, “txtM”, “txtL”, “txtXL”, “txtXXL”, “txt3XL”, “txt4XL” _

    )

    sizeLabels = Array( _

        “44”, “46”, “48”, “50”, “52”, “54”, “56”, “58”, “60”, _

        “S”, “M”, “L”, “XL”, “XXL”, “3XL”, “4XL” _

    )

    ‘ Find next empty row

    lastRow = wsData.Cells(wsData.Rows.Count, “A”).End(xlUp).Row + 1

    ‘ Insert basic product info

    wsData.Cells(lastRow, 1).Value = productionType

    wsData.Cells(lastRow, 2).Value = modelCode

    wsData.Cells(lastRow, 3).Value = colorCode

    wsData.Cells(lastRow, 4).Value = warehouseName

    ‘ Insert sizes (starting from column E)

    transactionsAdded = False

    For i = LBound(sizeTextBoxes) To UBound(sizeTextBoxes)

        On Error Resume Next

        quantityValue = Me.Controls(sizeTextBoxes(i)).Text

        On Error GoTo 0

        If quantityValue <> “” Then

            If IsNumeric(quantityValue) Then

                If CLng(quantityValue) > 0 Then

                    wsData.Cells(lastRow, 5 + i).Value = CLng(quantityValue)

                    transactionsAdded = True

                End If

            Else

                MsgBox “Invalid value in size field: ” & sizeLabels(i), vbExclamation

                Me.Controls(sizeTextBoxes(i)).SetFocus

                Exit Sub

            End If

        End If

    Next i

    If transactionsAdded Then

        MsgBox “Inventory entry saved successfully!”, vbInformation

        Call ClearForm

    Else

        MsgBox “No size quantities were entered. Please enter quantities to proceed.”, vbExclamation

    End If

End Sub

Clear Button:

Private Sub ClearForm()

    Me.txtprod.Text = “”

    Me.txtModel.Text = “”

    Me.txtColor.Text = “”

    Me.ComboBox1.Value = “”

    Dim sizeTextBoxes As Variant

    sizeTextBoxes = Array( _

        “txtSize44”, “TextBox1”, “TextBox2”, “TextBox3”, “TextBox4”, “TextBox5”, “TextBox6”, “TextBox7”, “TextBox8”, _

        “txtS”, “txtM”, “txtL”, “txtXL”, “txtXXL”, “txt3XL”, “txt4XL” _

    )

    Dim i As Long

    For i = LBound(sizeTextBoxes) To UBound(sizeTextBoxes)

        On Error Resume Next

        Me.Controls(sizeTextBoxes(i)).Text = “”

        On Error GoTo 0

    Next i

    Me.txtprod.SetFocus

End Sub

Close Button:
Private Sub CommandButton1_Click()

Unload Me

End Sub
That’s it the code is complete now the data will be inserting from Userform to Inventorysheet.

SAME LOGIC FOR OTHER FORMS BUTTON ARE USED JUST THE FORMS INSERTING DATA INTO OTHER SHEETS:

Next Step:
Setup Customer and Supplier Sheet:

Supplier Sheet:

Good Let’s try Inventory sheet By Adding Inventory data by Userform:

And it is working Perfectly As Shown In Above Image By the way Total in Coloumn U is calculated by =Sum()  formula:
Next Step Setup CurrentStock sheet like below image To Find
Total In Stock Summary.
Total Out Stock Summary.
Total Remaining Stock Summary.

We have Created dropdown in coloumn F and G For Code and color
then Use Formulas In cell H6 (=IF(G6=””,””,SUMIFS($B:$B,$A:$A,$F6&”-“&$G6&”-“&H$5)))
and then drag to right till w6 and then below to W12
here it is look like:

Same for Total out quantity: For cell AC6 (=IF(AB6=””,””,SUMIFS($C:$C,$A:$A,$F6&”-“&$G6&”-“&AC$5)))
same Drag and Drop,
Here how it look like,

Same for Total Remaining Stock Summary:

For cell AX6 (=IF(AW6=””,””,H6-AC6))
same Drag and Drop,
Her how it look like.

Totals Is calculated by below Formulas in image:

SO in that’s way CurrentStock Sheet is working in excel:

Next step is Products sheet In this sheet we are generating SKU MEAN The details of the products by coding:

Code for generating SKU(Product detail):
Sub GenerateSKUs()

    Dim wsSource As Worksheet

    Dim wsTarget As Worksheet

    Dim lastRow As Long, targetRow As Long

    Dim r As Long, c As Long

    Dim code As String, color As String, sizeLabel As String, sku As String

    Dim quantity As Variant

    Dim headerRow As Range

    Dim sizeStartCol As Long

        Set wsSource = ThisWorkbook.Sheets(“InventoryData”)

    Set wsTarget = ThisWorkbook.Sheets(“Products”)

        ‘ Clear previous data in PRINTBARCODE

    wsTarget.Range(“A2:A” & wsTarget.Rows.Count).ClearContents

        ‘ Find last row in source sheet

    lastRow = wsSource.Cells(wsSource.Rows.Count, 1).End(xlUp).Row

    targetRow = 2 ‘ Start writing SKUs from A2 in target sheet

        ‘ Assume headers are in Row 1, actual data from Row 2

    Set headerRow = wsSource.Rows(1)

        ‘ Find where sizes start (typically after Warehouse column)

    sizeStartCol = 5 ‘ Assuming sizes start at column E (5)

        For r = 2 To lastRow

        code = Trim(wsSource.Cells(r, 2).Value)   ‘ Code in Column B

        color = Trim(wsSource.Cells(r, 3).Value)  ‘ Color in Column C

                ‘ Skip rows with no code or color

        If code <> “” And color <> “” Then

            For c = sizeStartCol To headerRow.Cells(2, wsSource.Columns.Count).End(xlToLeft).Column

                quantity = wsSource.Cells(r, c).Value

                sizeLabel = Trim(headerRow.Cells(2, c).Value)

                                ‘ Only process numeric quantity > 0

                If IsNumeric(quantity) Then

                    If CLng(quantity) > 0 And sizeLabel <> “” Then

                        sku = code & “-” & color & “-” & sizeLabel

                        wsTarget.Cells(targetRow, 1).Value = sku

                        targetRow = targetRow + 1

                    End If

                End If

            Next c

        End If

    Next r

      MsgBox “SKU list generated in PRINTBARCODE sheet.”, vbInformation

End Sub
Formula For Cell B2 (=”(“&A2&”)”) And drag it to below.
Then apply IDAutomationHC39M Free Version Font On Coloumn To To Convert SKU into Barcode If not showing in font simply download and install from google font:

SAME LOGIC and coding just little bit changing in form label for Purchase and SalesScreen sheet:
and the working images of both forms are given below:
but supplier is getting from dropdown in Purchase sheet and same
customer in coloumn B is also getting from dropdown in SalesScreen sheet
IMAGES:

Create button for all forms

  1. In InventoryData Sheet Add Add inventory Button
  2. In Products Sheet Add UPDATE SKU Button
  3. In Purchase Sheet Add PURCHASE Button
  4. In SalesScreen Sheet Add SELL Button

AFTER adding all button Assign macro to its button

For example we are assigning macro to it:

FIRST Add shape caption Add inventory and assigh macro select the macro mean code we have write in VBA editor:

and then macro will be assigned to shape and now your shape will be working as Button

click on it and RUN Code, now fully automated and Beautiful sell Inventory is ready.

Thank you for reading and staying with us until the end. We look forward to meeting again in

Our next session, where we’ll dive into the highly requested topic of Excel automation

Leave a Reply

Your email address will not be published. Required fields are marked *