Form / Inventory

[ChrisLouie]

I have a form set up in Excel that is attached to an inventory list also in
Excel. I type in the qty wanted of a certain product and the name of the
product and the qty of the product in stock automatically populates. Is it
possible to have the qty in stock updated each time I enter a qty of the
product wanted... For example I need 3 of Product A and I have 5 in stock.
When I'm done filing out and printing the form the inventory list updates
showing that I now have 2 of Product A in stock.

 

[Mike]

Put this into a Sheet Module and test with cell A1

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Static productinstock As Double
With Target
If .Address(False, False) = "A1" Then
If Not IsEmpty(.Value) And IsNumeric(.Value) Then
productinstock = productinstock + .Value
Else
productinstock = 0
End If
Application.EnableEvents = False
.Value = productinstock
Application.EnableEvents = True
End If
End With
End Sub

 

[ChrisLouie]

I'm sorry that doesn't make sense to me.

Put this into a Sheet Module and test with cell A1

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Static productinstock As Double
With Target
If .Address(False, False) = "A1" Then
If Not IsEmpty(.Value) And IsNumeric(.Value) Then
productinstock = productinstock + .Value
Else
productinstock = 0
End If
Application.EnableEvents = False
.Value = productinstock
Application.EnableEvents = True
End If
End With
End Sub