Sheet name 'Inventory'
_______A________________B
1
2
3
4__Model number__________In Stock__________________'Update Inventory' command button to open userform
5__239-283-1_______________9
6__172-384-2_______________10
7__123-284-9_______________7
Just a cut out of the whole list. Below I'll have my code til now. I'm looking to update items in stock from my userform either
adding and/or subtracting from the number in stock(numavail).
If the cell 'In Stock' < 6 items change the background color of the cell to Yellow and if < 2 background color to red
When the userform is initialized
Private Sub UserForm_Initialize()
Dim lastRow As Long, cboCell As Range
'Combo box is populated with the range in Row A
With cboModelNumbers
contain the last row in column A
lastRow = Sheets(Inventory).Cells(Rows.Count, 1).End(xlUp).Row
'A for loop through each cell in Row A from A5
For Each cboCell In Range("A5:A" & lastRow)
'number 1 cont'd. If statement to test if cells have something in them
If Len(cboCell) > 0 Then cboModelNumbers.AddItem cboCell.Value
Next cboCell
'Combo box will have first/top model number visible in the combo box
cboModelNumbers.ListIndex = 0
End With
End Sub
'When the combobox has a change
Private Sub cboModelNumbers_Change()
'Preset numsold and numacquired text boxes to 0 and UpdateAm to same as numavail
txtNumSold.Value = 0
txtNumAcquired.Value = 0
txtUpdatedAm.Value = txtNumAvail.Value
'Whatever model number is selected from the combo box the number available for that model number will appear
Me.txtNumAvail.Value = Application.VLookup(Me.cboModelNumbers.List(Me.cboModelNumbers.ListIndex), _
Range("A5:B21"), 2, False)
End Sub
'When the command button 'UpdateStock' is clicked
Private Sub cmdUpdateStock_Click()
txtUpdatedAm.Value = txtNumAvail.Value - txtNumSold.Value + txtNumAcquired.Value
' and if statement to not allow the user to sell more than the available amount
If Val(txtNumSold.Value) > Val(txtNumAvail.Value) Then
MsgBox "You are unable to sell that many items for this model number"
'Reset the number in the sold text box to 0
txtNumSold.Value = 0
txtNumAcquired = 0
txtUpdatedAm.Value = txtNumAvail.Value
End If
'If the udpated amount is less than 0 without the sold amount being greater than the number available
If Val(txtUpdatedAm.Value) < 0 Then
MsgBox "You have tried to sell too many items", , "We don't have that many items to sell"
'Reset the number sold and acquired text boxes to 0
txtNumSold.Value = 0
txtNumAcquired.Value = 0
txtUpdatedAm.Value = txtNumAvail.Value
End If
End Sub
Private Sub cmdCancel_Click()
Unload.me
End Sub
Bookmarks