Inventory solution using worksheet selection change event

How to create an inventory solution using worksheet selection change event in Excel with VBA. A user wants to be able to select an inventory item in column 1 and automatically add item quantity purchased or subtract inventory sold. Also if any item quantity is below or equal to 2 then we should be able to display ‘re-order’ in the relevant worksheet cell. Watch the training video to learn how the inventory solution is implemented:

Watch this video on YouTube.

Here’s the complete VBA code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim strChoice As String
Dim sellQty As Long, buyQty As Long

If Target.Column = 1 And Target.Cells <> “” Then
strChoice = InputBox(“Buy or sell? Enter buy or sell.”, “Buy or Sell”)
If strChoice = “” Or IsNumeric(strChoice) Then
MsgBox “You can only enter the words buy or sell!”, vbInformation, “Enter buy or sell”
Exit Sub
End If

If strChoice = “sell” Then
sellQty = InputBox(“Enter the quantity sold as numerical value”, “Sold Quantity”)
If sellQty <= 0 Or Not IsNumeric(sellQty) Then
Exit Sub
End If
End If

If sellQty <= Target.Offset(0, 1).Value Then
Target.Offset(0, 1).Value = Target.Offset(0, 1).Value – sellQty
ElseIf sellQty > Target.Offset(0, 1).Value Then
MsgBox “Not allowed to sell more qty than in inventory!”, vbCritical, “Not Allowed”
Exit Sub
End If

If strChoice = “buy” Then
buyQty = InputBox(“Enter the quantity bought as numerical value”, “Purchased Quantity”)
If buyQty <= 0 Or Not IsNumeric(buyQty) Then
Exit Sub
ElseIf buyQty > 0 Then
Target.Offset(0, 1).Value = Target.Offset(0, 1).Value + buyQty
End If
End If

If Target.Offset(0, 1).Value <= 2 Then
Target.Offset(0, 2).Value = “Reorder”
Target.Offset(0, 2).Font.Color = vbRed
ElseIf Target.Offset(0, 1) > 2 Then
Target.Offset(0, 2).Value = “”
Target.Offset(0, 2).Font.Color = vbNormal
End If

End If

End Sub

Further reading:

Worksheet.SelectionChange Event (Excel)