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)

One thought on “Inventory solution using worksheet selection change event

  1. Jelay Andrada

    Hi! I am Jelay from Philippines. I want to create an inventory using excel here in our office. But I have a little knowledge in excel. Hoping that you could help me about my problem. The inventory goes like this. Every now and then we purchase computer set and deploy to diff. offices. Our concern is to know how many computers and software that are installed in their computers. And will know how many are the overall total. I wish you can help me with this and very grateful for it. Thank you so much!

    Reply

Leave a Reply

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