Inventory Management System

' Inventory Management System
' Main sheet (Inventory): Product Code (A), Name (B), Category (C),
'   Location (D), Qty in Stock (E), Reorder Level (F), Unit Cost (G),
'   Stock Value (H), Status (I)
' Movements sheet: Date (A), Product Code (B), Type (C), Quantity (D),
'   Reference (E), Notes (F)

Sub UpdateStockLevels()
    ' Recalculate stock values and check reorder levels
    On Error GoTo ErrorHandler

    Application.ScreenUpdating = False

    Dim wsInv As Worksheet
    Set wsInv = ThisWorkbook.Sheets("Inventory")

    Dim lastRow As Long
    lastRow = wsInv.Cells(wsInv.Rows.Count, "A").End(xlUp).Row

    If lastRow < 2 Then
        MsgBox "No inventory data found.", vbExclamation
        Exit Sub
    End If

    Dim lowStockItems As String
    Dim outOfStockItems As String
    Dim lowCount As Long
    Dim outCount As Long

    Dim i As Long
    For i = 2 To lastRow
        Dim qty As Double
        Dim reorderLevel As Double
        Dim unitCost As Double

        qty = Val(wsInv.Cells(i, 5).Value)
        reorderLevel = Val(wsInv.Cells(i, 6).Value)
        unitCost = Val(wsInv.Cells(i, 7).Value)

        ' Calculate stock value
        wsInv.Cells(i, 8).Value = qty * unitCost
        wsInv.Cells(i, 8).NumberFormat = "#,##0.00"

        ' Determine status
        Dim status As String
        If qty <= 0 Then
            status = "Out of Stock"
            outOfStockItems = outOfStockItems & wsInv.Cells(i, 2).Value & vbCrLf
            outCount = outCount + 1
            wsInv.Cells(i, 9).Interior.Color = RGB(255, 150, 150)
        ElseIf qty <= reorderLevel Then
            status = "Low Stock"
            lowStockItems = lowStockItems & wsInv.Cells(i, 2).Value & _
                " (Qty: " & qty & ", Reorder at: " & reorderLevel & ")" & vbCrLf
            lowCount = lowCount + 1
            wsInv.Cells(i, 9).Interior.Color = RGB(255, 230, 150)
        ElseIf qty <= reorderLevel * 1.5 Then
            status = "Adequate"
            wsInv.Cells(i, 9).Interior.Color = RGB(200, 230, 255)
        Else
            status = "In Stock"
            wsInv.Cells(i, 9).Interior.Color = RGB(200, 240, 200)
        End If

        wsInv.Cells(i, 9).Value = status
    Next i

    ' Format currency column
    wsInv.Range("G2:H" & lastRow).NumberFormat = "#,##0.00"
    wsInv.Columns("A:I").AutoFit

    Application.ScreenUpdating = True

    ' Show alerts
    Dim alertMsg As String
    If outCount > 0 Then
        alertMsg = "OUT OF STOCK (" & outCount & " items):" & vbCrLf & outOfStockItems & vbCrLf
    End If
    If lowCount > 0 Then
        alertMsg = alertMsg & "LOW STOCK (" & lowCount & " items):" & vbCrLf & lowStockItems
    End If

    If alertMsg <> "" Then
        MsgBox alertMsg, vbExclamation, "Stock Alerts"
    Else
        MsgBox "All stock levels are healthy.", vbInformation
    End If

    Exit Sub

ErrorHandler:
    Application.ScreenUpdating = True
    MsgBox "Error updating stock: " & Err.Description, vbCritical
End Sub

' ... 220 more lines hidden ...
'
' Sign up for free to view the complete code
' Visit: vbacode.io